Friday, March 14, 2014

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Replicate the problem:

create table parent (id int not null, name nvarchar(140), constraint pk_id primary key (id ));
create table child (id int not null , parent_id int  , constraint child_pk_id primary key (id ));
insert into child values (1,10);
insert into child values (2,20);
alter table child add constraint fk_parent_id foreign key (parent_id) references parent (id);


ERROR:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint " ". The conflict occurred in database " ", table " ", column ''.

PROBLEM:
We are trying to create foreign key on table, where the child table column values not found in parent table. (or parent table is empty)

SOLUTION:
Add associated values in parent your table, which shoul be reflect in child table.

insert into parent values (10, 'DBA');
insert into parent values (20, 'SE');
alter table child add constraint fk_parent_id foreign key (parent_id) references parent (id);   
Command(s) completed successfully.

No comments:

Post a Comment

web stats