Constraint for one-to-many relationship

Question

We have a two tables with a one-to-many relationship. We would like to enforce a constraint that at least one child record exist for a given parent record.

Is this possible?

If not, would you change the schema a bit more complex to support such a constraint? If so how would you do it?

Edit: I'm using SQL Server 2005

Answer 1

Such a constraint isn't possible from a schema perspective, because you run into a "chicken or the egg" type of scenario. Under this sort of scenario, when I insert into the parent table I have to have a row in the child table, but I can't have a row in the child table until there's a row in the parent table.

This is something better enforced client-side.

Answer 2

It's possible if your back-end supports deferrable constraints, as does PostgreSQL.

Answer 3

How about a simple non nullable column?

Create Table ParentTable
(
ParentID
ChildID not null,
Primary Key (ParentID), 
Foreign Key (ChildID ) references Childtable (ChildID));
)

If your business logic allows and you have default values you can query from the database for each new parent record, you can then use a before insert trigger on the parent table to populate the non nullable child column.

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
    ON ParentTable
    FOR EACH ROW 
BEGIN

    -- ( insert new row into ChildTable )
    -- update childID column in ParentTable 

END;

Answer 4

Here's an idea, in pseudo-SQL:

CREATE TABLE Parent (
    id integer primary key,
    child_relation_id integer not null references child_relation
);

CREATE TABLE child_relation (
    id integer primary key
);

CREATE TABLE child (
    id integer primary key,
    child_relation_id integer not null reference child_relation
);