The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

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);

Fruitless

Answer 1

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.

Danell

This was the answer for me, but I still struggled with realizing where the problem was, so I'll give a layman's example. If you have a table named 'Orders' and a table named 'Customers', and you've deleted some old customers, but not their orders, you will get this error if you decide to make a foreign key from Orders.CustomerId to Customers.Id. Some orders don't have a corresponding customer any more, so it is impossible to add the foreign key. – Provolone

Here's a query to check the incorrect values: select distinct referrerTable.referenceColumn from referrerTable left join referredTable on referredTable.referenceColumn = referrerTable.referenceColumn where referredTable.referenceColumn is null; – Mella

In a pinch, you could also use the "ALTER TABLE tablename WITH NOCHECK ..." option to add the FK. This will allow you to add the relation, even though existing data breaks the constraint. It's obviously better to clean up your data first, but this at least gives you another option. – Medina

@Medina If one deactivates database constraints "when it is desired", I would ask why to trouble oneself with them in the first place and not simply skip them if one is not interested in database integrity. – Danell

How, then, do you correctly insert without violating constraints? – Flush

@Flush e.g. before executing INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet) Values (8508284163,'Johanna','Backman',1); one had to insert the according entry in tblBana: INSERT INTO tblBana (BanNR) Values (8508284163); – Danell

This happened to me because a couple Id values in my table were 0 instead of NULL. I fixed it by setting every value in that field to NULL if it was currently 0. – Cobos

In my case, I had to remove all the entry from second table since first table was not having any records on my local. – Interatomic

Answer 2

This query was very useful for me. It shows all values that don't have any matches

select FK_column from FK_table
WHERE FK_column NOT IN
(SELECT PK_column from PK_table)

Surrey

Very simple and extremely useful when trying to find the faulty entries. Thanks a lot! – Thermion

If you have a multipart FK you can also do something similar with an 'except'... e.g. select C1, C2, C3 from FK_table except C1, C2, C3 from PK_table – Gossipmonger

Answer 3

Try this solution:

There is a data item in your table whose associated value doesn't exist in the table you want to use it as a primary key table. Make your table empty or add the associated value to the second table.

Fogged

this solution helped in a situation where a column was changed from null to not null, database publish in .NET was failing & this was able to solve it. – Guttersnipe

Answer 4

It is possible to create the foreign key using ALTER TABLE tablename WITH NOCHECK ..., which will allow data that violates the foreign key.

"ALTER TABLE tablename WITH NOCHECK ..." option to add the FK -- This solution worked for me.

Chinchy

Be aware that allowing such violations defeats the purpose of the foreign key constraint. – Rica

Dangerous...!!! Should only be used if you don't want to loose the data currently in the table. But even then, why not do a backup and then remove invalid Ids. – Credenza

I need to implement via java/spring/code to do that, not directly via SQL query, any ideia how to do this with the following code: @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.DETACH) @JoinTable(name = "tbUsuariosTipoOcorrencia", joinColumns = { @JoinColumn(name = "idUsuario") }, inverseJoinColumns = { @JoinColumn(name = "idTipoOcorrencia") }) and I did this do solve via database query: alter table tbUsuariosTipoOcorrencia WITH NOCHECK add constraint FKnbxg3ua7b8c5d53wps69q6jh foreign key (idUsuario) references tbUsuarios – Pushed

There is a perfectly legitimate use of this - if you intentionally want to have a relation to only some of the data, and you are stuck with the original tables structure and contents. But this situation does indicate a flaw in the original design. – Externalize

Answer 5

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

Cowans

Thank you, max. it worked for me if they have data even the relationships are perfect the Update-Database command won't work. – Platy

It is not necessary to remove any data as long as they are valid according to the foreign key being created. – Mella

@ΩmegaMan You are making edits on this page that add nothing & are unnecessary so should not be made. – Chanterelle

Answer 6

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

Demonolatry

Answer 7

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.

Badge

Answer 8

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.

Garpike

Answer 9

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.

Kkt

@agenc did i answer your question? – Kkt

Answer 10

When you define a Foreign Key in table B referencing the Primary Key of table A it means that when a value is in B, it must be in A. This is to prevent unconsistent modifications to the tables.

In your example, your tables contain:

tblDomare with PRIMARY KEY (PersNR):

PersNR     |fNamn     |eNamn      |Erfarenhet
-----------|----------|-----------|----------
6811034679 |'Bengt'   |'Carlberg' |10
7606091347 |'Josefin' |'Backman'  |4
8508284163 |'Johanna' |'Backman'  |1
---------------------------------------------

tblBana:

BanNR
-----
1
2
3
-----

This statement:

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

says that any line in tblDomare with key PersNR must have a correspondence in table tblBana on key BanNR. Your error is because you have lines inserted in tblDomare with no correspondence in tblBana.

2 solutions to fix your issue:

• either add lines in tblBana with BanNR in (6811034679, 7606091347, 8508284163)
• or remove all lines in tblDomare that have no correspondence in tblBana (but your table would be empty)

General advice: you should have the Foreign Key constraint before populating the tables. Foreign keys are here to prevent the user of the table from filling the tables with inconsistencies.

Palais

"Your error is because you have lines inserted in tblDomare with no correspondance in tblBana." That was it! – Campanulaceous

Answer 11

i had this error too as Smutje reffered make sure that you have not a value in foreign key column of your base foreign key table that is not in your reference table i.e(every value in your base foreign key table(value of a column that is foreign key) must also be in your reference table column) its good to empty your base foreign key table first then set foreign keys

Complaisance

Answer 12

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);

Inward

Answer 13

Smutje is correct and Chad HedgeCock offered a great layman's example. Id like to build on Chad's example by offering a way to find/delete those records. We will use Customer as the Parent and Order as the child. CustomerId is the common field.

select * from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null 

if you are reading this thread... you will get results. These are orphaned children. select * from Order Child left join Customer Parent on Child.CustomerId = Parent.CustomerId where Parent.CustomerId is null Note the row count in the bottom right.

Go verify w/ whomever you need to that you are going to delete these rows!

begin tran 
delete Order
from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null 

Run the first bit. Check that row count = what you expected

commit the tran

commit tran 

Be careful. Someone's sloppy programming got you into this mess. Make sure you understand the why before you delete the orphans. Maybe the parent needs to be restored.

Lowman

Thanks for the reply. I'm playing with stackoverflow database (gamedev actually) and found two NULLs when I LEFT JOIN Badges with Users. No wonder the constraints didn't work... – Washington

Answer 14

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.

Orr

Answer 15

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!

Archfiend

Answer 16

You should see if your tables has any data on the rows. If "yes" then you should truncate the table(s) or else you can make them to have the same number of data at tblDomare.PersNR to tblBana.BanNR and vise-verse.

Hogarth

Answer 17

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.

Cholinesterase

Answer 18

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

Neoplasty

Answer 19

Please first delete data from that table and then run the migration again. You will get success

Alexanderalexandr

Answer 20

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

Rimma

Answer 21

A foreign key constraint in a child table must have a parent table with a primary key. The primary key must be unique. The foreign key value must match a value in the patent table primary key

Gca

Answer 22

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.

Weinberg

Answer 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);

Ottavia

Answer 24

You're referencing it with a key that doesn't exist in the underlying table. This gives an error due to existing records inside. (because the content of the relationship is empty when you first create it)

You can either assign a value as default (my recommendation if there is data in the secondary table) or re-create the table you are referring to.

Disarm

Answer 25

or you can use this

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

Adequate

Answer 26

and just FYI, in case you do all of your data reference checks and find no bad data...apparently it is not possible to create a foreign key constraint between two tables and fields where those fields are the primary key in both tables! Do not ask me how I know this.

Traitor