Integrity: How to enforce at least one child record

Question

Integrity: How to enforce at least one child record

Richard Fortier

SSC Enthusiast

Points: 181
More actions
October 25, 2006 at 8:17 am

#94802

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 ?
I.e.
    insert into parent values ('a')
should fail, but
    begin tran;
    insert into parent values ('a');
    insert into child values ('a', 'a');
    commit tran;
should work.
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 !!).
Thanks a lot.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

You can't enforce this with foreign key constraints because you would essentially be making each table both a parent and a child. You wouldn't be able to insert or delete from either table.

If you want to ensure that a parent and child are added at the same time by an application, you'll have to enforce that in the application by requiring the entry of enough information to do both inserts.

Greg

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

i'm pretty new at this SQL thing myself, but if you have a FK on a table I thought you needed to have the data in another table with a PK before you can insert data with a FK

DaveItz SSCrazy Points: 2146 More actions · Answer 3

Yes.

If you have that relationship (and enforce it) then that is what would happen. He was talking about the reverse, though, ensuring that you can't accidentally create a childless row in the parent table (not a parentless row in the child table).

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 4

During parent table insert you have to create one child or have a trigger on parent to create one child.

Similarly have a delete trigger on Child and if the child being deleted is the only child of parent then delete the parent as well. Other than that I don't know if you can achieve what you want.

Thanks

Sreejith

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

How about this :

Create the foreign key between master (PK) and childs(FK)

Create stating table with the same structure as the child table

Create an instead of trigger on the child table. If a master record exists : Insert into the real child table. If not, then insert in the staging table.

Create a proc that insert the child records to the perm table and insert all the childs you need wether a master record exists or not.

Create another proc that insert to the master table.

create a trigger on the child table for deletes. If no more child exists then either delete the master record or raiserror depending on the business needs.

Create a trigger on the master table for insert : insert the child rows from the staging table to the real table

if rowcount = 0 then rollback transaction and raiserror.

else delete the child records from staging

Method of use for new master record :

Insert all child records you need.

Inserts are bounced to staging table (the FK doesn't error out because of the instead of insert trigger which redirects the records to the right table)

Insert the master record when at least one or all child records are inserted

Method of use for existing master record :

Just insert childs as needed, they will be redirected to the real table.

Did I forget anything? Do you see any pitfalls using this method?