The FOREIGN KEY restriction was in conflict with the ALTER TABLE command.

Problem

What causes this problem when you add a foreign key to the tblDomare table?

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

Asked by user3162932

Solution #1

You got this error because you tried to make a foreign key out of tblDomare. tblBana to PersNR BanNR but/and the tblDomare values PersNR did not match any of the tblBana values. BanNR. You can’t make a relationship that isn’t referentially sound.

Answered by Smutje

Solution #2

This query was quite beneficial to me. It displays all values for which there are no matches.

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

Answered by dantey89

Solution #3

Try this solution:

Answered by PatsonLeaner

Solution #4

ALTER TABLE tablename WITH NOCHECK… can be used to construct the foreign key, allowing data that violates the foreign key to be stored.

To add the FK, use the “ALTER TABLE tablename WITH NOCHECK…” option. For me, this solution worked.

Answered by Ankita Biswas

Solution #5

A column value in a foreign key table should, I believe, equal the main key table’s column value. It will throw the message if we try to build a foreign key constraint between two tables where the value inside one column (which will be the foreign key) differs from the column value of the primary key table.

As a result, it’s best to only put values in the Foreign key column that are also present in the Primary key table field.

For example, if the Primary table column has values 1, 2, and 3, but the values entered in the Foreign key column are different, the query would fail because it wants the values to be between 1 and 3.

Answered by sam05

Post is based on https://stackoverflow.com/questions/21839309/the-alter-table-statement-conflicted-with-the-foreign-key-constraint