Unanswered: Enforcing cardinality in a relationship
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?
Many thanks in anticipation!
10Pints ... "will move no more" (till the pubs open anyway)
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.
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.