how to implement data constraints to enforce minimum number child objects?

Suppose each AssetGroup object must have at least 1 Asset object . How to enforce this constraint in : a) traditional SQL b) NHibernate

I can check the number of children Asset before delete operation , but perhaps there are more declarative ways to do it .

In case of creating AssetGroup, does it force me to create an Asset first before creating an A开发者_运维知识库ssetGroup ?

You're trying to deal with that at the wrong level.

This is a business-level constraint, not a data-level one. Do the validation in your business layer and you'll be fine.

By "business layer" I mean either the entities themselves, or whatever you have on top of the data (NH/Repository) layer.

Three possible ways:

1. Attach trigger to any of tables, which will do count(*) before commit.
2. Add field 'memberCount' to you AssetGroup and check constraint, which ensures (memberCount <=1), then on insert do: a) manual 'update set memberCount = memberCount + 1'
   b) do this via trigger (looks like 1., but with stored state)
3. Use locks and check at application level. (as mentioned by Diego)