CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
EvilDr
EvilDr

Reputation: 9630

Create Exclusive OR constraint across two columns

I have a table with two columns (amongst others):

NotApplicable  bit
TargetLevel    numeric(5,2)

I need to create a constraint whereby the following rules apply:

  1. NotApplicable and TargetLevel cannot both be NULL
  2. NotApplicable and TargetLevel cannot both have values
  3. NotApplicable or TargetLevel must have a value

I guess this is an exclusive OR scenario? I had a go a while back, but I realised now that this doesn't account for the last scenario above:

ALTER TABLE [dbo].[my_Table] ADD CONSTRAINT [DF_tbl_my_Table_notApplicable] DEFAULT ((0)) FOR [notApplicable]
GO
ALTER TABLE [dbo].[my_Table] WITH CHECK ADD CONSTRAINT [CK_tbl_my_Table] CHECK  ((COALESCE([targetLevel],[notapplicable]) IS NOT NULL))
GO
ALTER TABLE [dbo].[my_Table] CHECK CONSTRAINT [CK_tbl_my_Table]
GO

Any help perfecting this would be appreciated. Also, is the last ALTER statement actually needed in the example above please?

Upvotes: 3

Views: 1502

Answers (2)

Mark Freeman
Mark Freeman

Reputation: 1155

An alternative syntax for the two column scenario is:

ALTER TABLE T ADD CONSTRAINT CK_One_Or_Tother CHECK (
     (NotApplicable IS NULL     OR TargetLevel IS NULL)
 AND (NotApplicable IS NOT NULL OR TargetLevel IS NOT NULL)
)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

It's a bit verbose, but the standard way I do it is just:

ALTER TABLE T ADD CONSTRAINT CK_One_Or_Tother CHECK (
    (NotApplicable IS NULL and TargetLevel IS NOT NULL) OR
    (NotApplicable IS NOT NULL and TargetLevel IS NULL)
)

Also, is the last ALTER statement actually needed in the example above please?

ALTER TABLE [dbo].[my_Table] CHECK CONSTRAINT [CK_tbl_my_Table]

This is only necessary if you have ever created or altered the constraint and specified NOCHECK, or have in some other way disabled the constraint previously and now enabled it. The default for creation of constraints is for them to be enabled.

For a large number of columns, for which exactly one must be filled in, I tend to switch to an alternative structure:

ALTER TABLE T ADD CONSTRAINT CK_One_Or_Tother CHECK (
    1 = (
        CASE WHEN ColumnA IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN ColumnB IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN ColumnC IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN ColumnD IS NOT NULL THEN 1 ELSE 0 END
    )
)

Which has the advantage of only naming each column once, but can look a bit ugly.

Upvotes: 8

Related Questions