Constraint for one-to-many relationship

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

From stackoverflow

• 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.

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

  Tony Andrews : And as does Oracle

• 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;
  

  Matt Kane : This doesn't allow for a one-to-many relationship.

• 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
  );