Sunday, March 2, 2014

Could not create constraint. See previous errors.

Execute below query to get the error:

create table parent(id int , name varchar(30));
create table child(id int, parent_id int);
alter table child alter column id int not null;
alter table child add constraint pk primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table '' that match the referencing column list in the foreign key ''.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Cause:
We are trying to create a FOREIGN KEY constraint on a table and the column being referenced as a FOREIGN KEY is not a
PRIMARY KEY on the other table.

SOLUTION:1
Add primary_key on referenced column.
alter table parent alter column id int not null;
alter table parent add constraint pk_parent primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:2
create unique index idx_uni on parent (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:3
alter table parent add constraint uq_parent_id unique (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

No comments:

Post a Comment

web stats