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.