5

I have 3 tables. A base table, call it Table A, and two tables that reference Table A, Call them Table X and Table Y. Both X and Y have a foreign key contraint that references Table A. The Foreign Key of X and Y is also their own Primary Key.

I'd like to know if it is possible to add a constraint that will only allow one of these tables to contain a recrod that references Table A. So if X has a record that references A then Y can't have one and if Y has a record that references A then X can't have one.

Is this possible?

Thanks,

Duncan Gravill
  • 4,552
  • 7
  • 34
  • 51
  • I'd be curious about your use case. There is something __interesting__ about your structure. You have 3 primary keys using the same key value. – NullRef Jun 12 '11 at 21:46
  • Yes they are three tables for storing user accounts. There are two types of account. So there is a base table that contains info that is common to both type of account and then a table for each account type that holds info specific to that account type. So the PK for each table is the UserID because I figured this would make things coherent and make accessing a record by UserID easy. – Duncan Gravill Jun 14 '11 at 13:08

2 Answers2

7

CHECK constraints with UDFs (which is Oded's answer) don't scale well and have poor concurrency. See these:

So:

  • create a new table, say TableA2XY
  • this has the PK of TableA and a char(1) column with a CHECK to allow ony X or Y. And a unique constraint on the PK of A too.
  • tableX and tableY have new char(1) column with a check to allow only X or Y respectively
  • tableX and tableY have their FK to TableA2XY on both columns

This is the superkey or subtype approach

  • all DRI based
  • no triggers
  • no udfs with table access in CHECK constraints.
jcolebrand
  • 15,889
  • 12
  • 75
  • 121
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I am doing pretty much the same thing as what you have described. Recently I switched to adding a persisted computed CHAR(1) column in my child table, so that it does not show up in column lists automatically generated by SQL Prompt – A-K Jun 12 '11 at 21:19
1

Yes, this is possible using CHECK constraints.

Apart from the normal foreign key constraint, you will need to add a CHECK constraint on both referencing tables to ensure that a foreign key is not used in the other referencing table.

Oded
  • 489,969
  • 99
  • 883
  • 1,009