Circular referential constraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Circular referential constraints

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2007-01-10 : 16:15:07

I am struggling with getting circular referential constraints to work.
Suppose I have a Parent table (id, first_child) and Child table (id, parent). Naturally, there is an FK constraing between Parent.first_child and Child.id and another FK between Child.parent and Parent.id. Suppose I also need to insert one row in Parent and one row in Child. I can get this to work (see the script below).

The problems arises when I am trying to force Parent.first_child and Child.parent to be NOT NULL. This is required for the data consistency. However, in this case I start getting exception when trying to insert any row with FK field value NULL.

What is the right way to enforce circular referential constraints and require all values to be NOT NULL when any transaction commits?

Below is a script that will reproduce the use case scenario above.

Thanks.

Alec

-- create tables
create table parent (id int primary key, first_child int not null)
create table child (id int primary key, parent int references parent(id))
alter table parent add constraint fk_first_child foreign key (first_child) references child(id)

-- insert into tables
begin transaction
insert into parent(id, first_child) values (1, null)
insert into child(id, parent) values (1, 1)
update parent set first_child = 1
commit transaction

-- view tables
select * from parent
select * from child

-- drop tables
/*begin transaction
alter table parent drop constraint fk_first_child
drop table child
drop table parent
commit transaction*/

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-10 : 18:03:24

Just a couple of dummy rows that you can use for the inserts later before you add the constraint.

create table parent (id int primary key, first_child int not null)
create table child (id int primary key, parent int references parent(id))
insert into parent(id, first_child) values (0, 0)
insert into child(id, parent) values (0, 0)
alter table parent add constraint fk_first_child foreign key (first_child) references child(id)

Now when you insert, just specify the first child as 0 in the parent insert, and then update it as you were.

begin transaction
insert into parent(id, first_child) values (1, 0)
insert into child(id, parent) values (1, 1)
update parent set first_child = 1
commit transaction

lebedev
Posting Yak Master

126 Posts

Posted - 2007-01-11 : 13:05:48

Thanks for the advice, but I don't think your solution is any better than allowing nulls in the referencing tables.
Am I completely misunderstanding DB concepts or SQL Server is having an inherent problem implementing circular FK constraints?
Thanks.

Alec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources