Hi, must be either easy or not do-able I guess;
I want to enforce a 1-to-many relationship between table A (parent) and B (child)
where between 1 and 5 rows in B should exist for each row in A .... well that is the spec. I suppose it should be 0..5 originally else how can you create a row in A before a related row in B - I expect that B will have an FK into A in order to implement the 1-to-many in the first place.

I can do a normal referential integrity on B, but can I do more to limit to 0..5 rows in B for a given A?

Any thoughts?

Many thanks in anticipation!

10Pints ... "will move no more" (till the pubs open anyway)

Yes, you should start with a "classic" FK constraint. After that extra triggers on the child to check the INSERTS: if you are the 6th child .... abort

If the child rows are identified in any way to be the 1st, 2nd, etc. to 5th child (e.g. with a sequence number), you could use a check constraint on the corresponding column, in addition to the normal referential constraint.

Gentlemen! what sense! Many thanks.

I have since gone into a meet about this and it transpires that we will probably just want the Db to do a simple FK constraint and leave the cardinality issue to configuration (XML/c++ code). But I do like the idea of the trigger and the sequence - it immediately prompted to me the question - does order matter? - in our case - I had blindly gone with the map construct implied by the design - it transpires that order does not matter but getting it 'out of the door' does.

Now where have I heard that before ...

Much appreciated gentlemen - both good solutions to the issue.