sql - Why can't I add this foreign key? -

i'll post main part. have 2 tables, each 1 has have pk of other fk.

create table apartment (      cod_apartment int not null primary key,      cod_offer int not null );  create table offer (     cod_offer int not null primary key,     cod_apartment int not null ); 

first inserted values on both tables , working, search using "select * from...". tried add foreign key:

this worked.

alter table offer add foreign key (cod_apartment ) references apartment; 

and not.

alter table apartment add foreign key (cod_offer) references offer; 

this error message:

the alter table statement conflicted foreign key constraint "fk__apartment__cod_offer__6383c8ba". conflict occurred in database "kleber_apartment", table "dbo.offer", column 'cod_offer'.

the problem is, every time try execute, fk name changes. , fk doesn't exist. dropped both tables , tried insert values again, same happens.

what be?

that means you're trying add foreign key when existing data doesn't obey constraint. have record in apartment table cod_offer column not match value in cod_apartment table.

adding foreign key not constrains future data, requires existing data must follow rule.

and regarding 6383c8ba, whenever add constraint without giving name, sql server picks 1 you. personally, i'd recommend like:

alter table dbo.apartment add constraint fk_apartment__cod_offer foreign key (cod_offer) references dbo.offer (cod_offer); 

this lets define names way want, , little more clear you're building.