Home/ Questions/What is the the alter table statement conflicted with the foreign key constraint problem, and how to solve it?
Next
Answered
Ahmed Lam
  • 11
Asked: 2022-05-18T17:46:06+00:00 In:

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

  • 11

. Advertisement .

..3..

. Advertisement .

..4..

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?

foreign key constraint
Share

2 Answers

  1. Best Answer
    Expert

    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.

    • 0

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

    • 14

Leave an answer

You must login to add an answer.

Need An Account,