0

I have a parent table with a relationship to each of four child tables. The primary key of the parent table forms both the primary and foreign key for the child tables. For any given row in the parent table, there can only be two child tables associated, either GradeOneA and GradeOneB or GradeTwoA and GradeTwoB. How can I add a constraint that if an ID in the parent table matches an ID in the GradeOneA table then it cannot also match an ID in the GradeTwoA table and vice versa?

user3632714
  • 97
  • 9
  • 2
    Please post an relevant example and the table definitions. – CiucaS Dec 05 '14 at 12:01
  • 1
    Yeah. Generally I would say "fire the guy doing the data model". – TomTom Dec 05 '14 at 12:07
  • @TomTom: The scenario is data collection and there are two forms to be completed and therefore two tables to store the data (called A and B here). In one scenario we need to complete the first set of two forms, the alternative scenario requires the second set of two forms instead. The parent table is designed to store the common data and link the two tables containing the data collected. How else would you model it? – user3632714 Dec 05 '14 at 12:43
  • @user3632714 i agree with TomTom and even if you supplied no technical detail to me also this sound as a suboptimal structure definition. SO is not the right place to dig into the issue because of its complexity; you should ask for support to someone experienced at your site (a colleague or a consultant). – Paolo Dec 05 '14 at 13:07
  • I didn't think it was such a complex question. If it was MySQL I'd have written a BEFORE INSERT, UPDATE trigger with an IF EXISTS condition to make sure the ID didn't already exist in the other table, but as MSSQL doesn't have a BEFORE trigger structure I thought that it might be possible with a constraint. – user3632714 Dec 05 '14 at 15:27
  • SQL Server has an `INSTEAD OF` trigger which provides similar function. Give it a try. – Code Different Dec 05 '14 at 21:12

1 Answers1

0

You can do this with a CHECK constraint that calls a function on each of the child tables. The function checks the other child tables to see how many of them have the same PK/FK. The constraint looks at the return value and decides whether the row should be allowed.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52