Integrity: How can I enforce at least one child record
Posted on 2006-10-26
That question has probably been asked before, but I haven't found a trace.
I have the following:
create table parent (
code varchar(100) not null primary key);
create table child (
parentCode varchar(100) not null foreign key references parent(code),
code varchar(100) not null);
How can I make sure a record in 'parent' can't exist without at least one record in 'child' ? I can put a trigger for the deletion of 'child', that's easy, but what about insertions ?
insert into parent values ('a')
should fail, but
insert into parent values ('a');
insert into child values ('a', 'a');
This seems like such a standard issue, I'm surprised it's not built in FOREIGN KEY constraints already (or I haven't found it !!).
I'm using SQL Server 2005.
One solution I can think of is postponing the 'real' validation until commit-time (à la Oracle's 'deferred'), but I don't know how !
Thanks a lot.