Is it possible to constrain a table to have a value in only one of a set of columns

Question

I have a table which needs to link one of three seperate tables, but it should only link to one of them, e.g.

Main_Table
id UNIQUEIDENTIFIER
t1_id UNIQUEIDENTIFIER
t2_id INT
t3_id INT

T1
id UNIQUEIDENTIFIER
name VARCHAR(255)

T2
id INT
name VARCHAR(255)

T3
id INT
name VARCHAR(255)

Is it possible to have a constraint whereby only one of t1, t2 or t3 is not null at any one time?

Is this just bad design? If so, what suggestions would you make for the design?

EDIT:

I've been asked to elaborate the reasons behind this particular design.

Main_Table is attempting to be a payer table, which could reference either an individual user (T1), a group of individual users (T2), or a group of groups (T3).

This is a database design I've inherited, and it isn't really subject to change unfortunately.

My biggest problem is that I need to associate between different types, so a type field won't work here as the indexes are different.

Answer 1

besides the bad design, if you can't change it, it's possible to use a trigger to enforce this constraint

Answer 2

An alternative design could be to have only one key and a "keytype" field containing a value say 1, 2 or 3 indicating which table it links to; aside from being extendable, this structure intrinsically the "link to only one table constraint". If you wish to stay with current design, contraints won't do, you'll need a trigger.=.

Answer 3

The design you're describing is called exclusive arcs. Yes, it's a pretty fragile design and even fails some rules of normalization.

Here's an alternative:

Main_Table
id UNIQUEIDENTIFIER
t_id INT NOT NULL
  FOREIGN KEY (t_id) REFERENCES T0 (id)

T0
id UNIQUEIDENTIFIER
type INT NOT NULL CHECK (type IN (1,2,3))
  UNIQUE KEY (id, type)

T1
id INT 
type INT NOT NULL CHECK (type = 1) 
name VARCHAR(255)
  FOREIGN KEY (id, type) REFERENCES T0 (id, type)

T2
id INT
type INT NOT NULL CHECK (type = 2)
name VARCHAR(255)
  FOREIGN KEY (id, type) REFERENCES T0 (id, type)

T3
id INT
type INT NOT NULL CHECK (type = 3)
name VARCHAR(255)
  FOREIGN KEY (id, type) REFERENCES T0 (id, type)

With this design, each row in Main_Table must reference one row in T0.
Likewise, each row in T0 can be the parent of only one row in T1, T2, or T3.

This is a way to implement Class Table Inheritance and Polymorphic Associations without breaking referential integrity.

---

Main_Table is attempting to be a payer table, which could reference either an individual user (T1), a group of individual users (T2), or a group of groups (T3).

Right, so think of this in terms of object-oriented design. If you had three classes that could function as a recipient of payments, you'd create a interface called Payable or something, so that each you could rely on typing those objects. All Payable objects must have a sendPayment() method for instance. In some OO languages, the interface is a superclass and is called an abstract class or a pure virtual class.

The T0 table functions as a common type for each of the child tables T1, T2, and T3. When Main_Table has a foreign key to T0, it's like saying Main_Table must have a reference to some entity that is Payable, but any object descending from that superclass is okay to use.

The type column is just a trick to make sure that a given T0.id can be referenced only by one subclass table at a time. It's kind of optional, if you can rely on your application logic to insert a given child row into only one of the subclass tables.

---

Also see the section on Polymorphic Associations in my presentation "SQL Antipatterns Strike Back."

Answer 4

If your DB has check constraints, you could whip up an ugly kludge like:

ALTER TABLE Main_Table
 add constraint CK_ThisWorksButItsUgly
  check ( (  case when t1_id is null then 0 else 1 end
           + case when t2_id is null then 0 else 1 end
           + case when t3_id is null then 0 else 1 end) = 1)

Some of the syntax may be wrong there, but you get the idea. It'd probably perform well enough--the check would only have to fire when one of the columns got modified--but no way is it pretty.

Bill Karwin's Exlusive Arcs are very cool, if you can re-architect the database design.

Answer 5

This is yet another instance of the gen-spec pattern.

Go ogle web articles on "generalization specialization relational modeling". There are some excellent ones out there.