# Thread: Enforcing cardinality in a relationship

1. Registered User
Join Date
Nov 2010
Posts
17

## 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?

Any thoughts?

Many thanks in anticipation!

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

2. Registered User
Join Date
Jan 2009
Location
Zoetermeer, Holland
Posts
746
Originally Posted by 10Pints
I can do a normal referential integrity on B
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

3. :-)
Join Date
Jun 2003
Location
Posts
5,516
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.

4. Registered User
Join Date
Nov 2010
Posts
17

## Out of doors: sequence/FK

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.