What is the the alter table statement conflicted with the foreign key constraint problem, and how to solve it?

Question

I encountered the following problem in completing my work:

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'.

Below is the code I ran:

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

What's causing it, and how can it be resolved in the “the alter table statement conflicted with the foreign key constraint“ in the sql?

lyytutoria · Accepted Answer

The cause: There are 2 reasons of your error: 1. A data item in your database has an associated value that does not exist in the table which you want to utilize as a primary key. 2. The values of foreign key in tblBana.BanNR is not assorted with any values in tblDomare.PersNR. Solution: To solve this problem, you have to find all values that are not compatible by doing as below:

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

Or you can remove all values in your table or add the value to the second table. Using ALTER TABLE tablename WITH NOCHECK to generate the foreign key is not bad solution, it will permit data that transgresses the foreign key.

Agathe Guibert · Answer

This happened because you attempted to create a foreign code from tblDomare.PersNRto tblBana.BanNR, but/or the values of tblDomare.PersNR weren't compatible with tblBana.BanNR. It is illegal to create a relation that violates referential integrity.

Sign Up

Sign In

Forgot Password

ITtutoria Latest Questions

What is the the alter table statement conflicted with the foreign key constraint problem, and how to solve it?