Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by 10Pints View Post
    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. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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. #4
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •