Конфликт инструкции alter table с ограничением foreign key sql

Why does add a foreign key to the tblDomare table result in this error?

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK__tblDomare__PersN__5F7E2DAC». The conflict occurred in database «almu0004», table «dbo.tblBana», column ‘BanNR’.

Code

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
PRIMARY KEY (PersNR));

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (7606091347,'Josefin','Backman',4);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (8508284163,'Johanna','Backman',1);

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL,
PRIMARY KEY (BanNR));

INSERT INTO tblBana (BanNR)
Values (1);

INSERT INTO tblBana (BanNR)
Values (2);

INSERT INTO tblBana (BanNR)
Values (3);

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

It occurred because you tried to create a foreign key from tblDomare.PersNR to tblBana.BanNR but/and the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR. You cannot create a relation which violates referential integrity.

Remove all existing data from your tables and then make a relation between the tables.

Before You add Foreign key to the table, do the following

1. Make sure the table must empty or The column data should match.
2. Make sure it is not null.

3. If the table contains do not go to design and change, do it manually.

   alter table Table 1 add foreign key (Column Name) references Table 2 (Column Name)

   alter table Table 1 alter column Column Name attribute not null

I guess, a column value in a foreign key table should match with the column value of the primary key table. If we are trying to create a foreign key constraint between two tables where the value inside one column(going to be the foreign key) is different from the column value of the primary key table then it will throw the message.

So it is always recommended to insert only those values in the Foreign key column which are present in the Primary key table column.

For ex. If the Primary table column has values 1, 2, 3 and in Foreign key column the values inserted are different, then the query would not be executed as it expects the values to be between 1 & 3.

answered Nov 27, 2014 at 15:46

In very simple words your table already has data present in it and the table you are trying to create relationship with does have that Primary key set for the values that are already present.

1. Either delete all the values of the existing table.
2. Add all the values of foreign key reference in the new table.

answered Oct 27, 2021 at 10:27

Try DELETE the current datas from tblDomare.PersNR . Because the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR.

answered Feb 2, 2015 at 6:06

the data you have entered a table(tbldomare) aren’t match a data you have assigned primary key table. write between tbldomare and add this word (with nocheck) then execute your code.

for example you entered a table tbldomar this data

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

and you assigned a foreign key table to accept only 1,2,3.

you have two solutions one is delete the data you have entered a table then execute the code. another is write this word (with nocheck) put it between your table name and add
like this

ALTER TABLE  tblDomare with nocheck
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

From our end, this is the scenario:

1. We have an existing table in the database with records.
2. Then I introduces a NOT nullable foreign key
3. After executing the update i got this error.

How did i solve you ask?

SOLUTION: I just removed all the records of the table, then tried to update the database and it was successful.

answered Jun 1, 2021 at 12:46

This happens to me, since I am designing my database, I notice that I change my seed on my main table, now the relational table has no foreign key on the main table.

So I need to truncate both tables, and it now works!

answered Mar 16, 2018 at 17:46

answered Oct 23, 2018 at 21:44

In my scenario, using EF, upon trying to create this new Foreign Key on existing data, I was wrongly trying to populate the data (make the links) AFTER creating the foreign key.

The fix is to populate your data before creating the foreign key since it checks all of them to see if the links are indeed valid. So it couldn’t possibly work if you haven’t populated it yet.

answered Aug 9, 2019 at 11:08

I encounter some issue in my project.

In child table, there isn’t any record Id equals 1 and 11

I inserted DEAL_ITEM_THIRD_PARTY_PO table which Id equals 1 and 11 then I can create FK

answered Oct 21, 2019 at 6:27

answered Apr 19, 2020 at 16:43

I had the same problem.
My issue was having nullable: true in column (migration file):

AddColumn("dbo.table", "column", c => c.Int(nullable: true));
        

Possible Solutions:

1. Change nullable ‘false’ to ‘true’. (Not Recommended)
2. Change property type from int to int? (Recommended)

And if required, change this later after adding column > then missing field data in previous records

If you’ve changed an existing property from nullable to non-nullable:
3) Fill the column data in database records

answered Apr 29, 2021 at 12:15

answered May 1, 2021 at 15:52

When you alter table column from nullable to not nullable column where this column is foreign key, you must :

1. Firstly, initialize this column with value (because it is foreign
   key not nullable).

2. After that you can alter your table column normally.

answered Sep 17, 2021 at 6:23

Please try below query:

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL PRIMARY KEY,

);

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL PRIMARY KEY,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
FK_tblBana_Id VARCHAR (15) references  tblBana (BanNR)
);


INSERT INTO tblBana (BanNR)
Values (3);


INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet,FK_tblBana_Id)
Values (8508284173,'Johanna','Backman',1,3);

answered Dec 9, 2021 at 7:01

or you can use this

SELECT  fk_id FROM dbo.tableA
Except
SELECT fk_id From dbo.tableB

answered Jul 10, 2017 at 15:13

I am trying to add a new foreign key to an existing table where there is data in the column I am wanting to make a change to.

In dev, I have tried this where data does and does not exist. Where there is no data this works fine.

ALTER TABLE [rpt].ReportLessonCompetency WITH CHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

Where there is data I get the following error

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint «FK_Grade_TraineeGrade_Id». The conflict occurred in database «T_test», table «Core.Report.TraineeGrade», column ‘Id’.

I would be grareful if someone could let me know what I need to do in order to ensure that this works where data does and does not exist as I cannot control if the live database will or will not have any existing data.

Thanks

Simon

asked Jul 12, 2019 at 6:22

You can avoid verifying FOREIGN KEY constraints against existing data by using WITH NOCHECK.

ALTER TABLE [rpt].ReportLessonCompetency WITH NOCHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

I wouldn’t recommend doing this as ignored constraint violations can cause an update to fail at a later point. You should clean up your data instead.

answered Jul 12, 2019 at 8:44