The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

I have a problem when trying to add a foreign key to my tblDomare table  what am I doing wrong here   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     Error message      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

User · Answer

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.

User · Answer

Clean your data from your tables and then make relation between them

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Before You add Foreign key to the table  do the following   Make sure the table must empty or The column data should match  Make sure it is not null  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  3

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to foreign-keys