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

Answer 1

It has occurred because you have tried to create a foreign key from tblDomare.PersNR to tblBana.

BanNR but/and the values in tblDomare.PersNR did not match with any of the values in tblBana.BanNR. 

Note: You are not supposed to create a relationship that violates referential integrity.