19

joking with a collegue, I came up with an interesting scenario: Is it possible in SQL Server to define a table so that through "standard means" (constraints, etc.) I can ensure that two or more columns are mutually exclusive?

By that I mean: Can I make sure that only one of the columns contains a value?

Andrea Colleoni
  • 5,919
  • 3
  • 30
  • 49
Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • what problem are you trying to solve? subnormal databases are a PITA to work with, maybe you can do it in a way that doesn't sacrifice the relational goodies. – just somebody Dec 02 '09 at 10:44
  • Well, there's not an actual problem behind that idea. I just wanted to know if it is technically possible to do that. One idea MIGHT be the requirement that a user may either enter his own name or the company name, but not both. I guess thinking hard I might come up with something better :-) – Thorsten Dittmar Dec 02 '09 at 10:50

1 Answers1

23

Yes you can, using a CHECK constraint:

ALTER TABLE YourTable
ADD CONSTRAINT ConstraintName CHECK (col1 is null or col2 is null)

Per your comment, if many columns are exclusive, you could check them like this:

case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end
= 1

This says that one of the four columns must contain a value. If they can all be NULL, just check for <= 1.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Ah yes, I can see where this is going. However, the check would be much more complex if I have three or more columns, as I'd have to add every possible combination, right? – Thorsten Dittmar Dec 02 '09 at 10:51
  • @Thorsten Dittmar: It does get a bit more complex with multiple columns, I think you can do it without adding every possible combination, answer edited – Andomar Dec 02 '09 at 11:24
  • You can also use a trigger if the check is too complex for a check constraint. Just be sure the trigger can handle multiple record inserts and updates. – HLGEM Feb 04 '16 at 14:43