Execute below query to get the error:
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
SOLUTION:2
SOLUTION:3
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