Saturday, March 23, 2013

T-SQL One to One Can't Be Done

I'm preparing a series on Entity Framework, and discovered something yesterday I never knew. In MS SQL Server, you can't successfully model a true one-to-one relationship, where both sides are required. I say "successfully," because you can configure the tables, but they won't work. This is because SQL won't allow deferred constraint resolution, which is fine by me.

The insert statements, below, will fail. Note you have to remove one of the foreign key constraints before you can drop the tables!

create table One (
 OneId int not null identity primary key,
 Name varchar(10) null

create table Two (
 OneId int not null primary key references One(OneId),
 Name varchar(10)

alter table One add constraint fk1 foreign key (OneId) references Two(OneId)

insert One(Name) values('asdf')
insert Two(Name) values('qwer')

alter table One drop constraint fk1
drop table Two
drop table One