Revisions to Check constraint only one of three columns is non-null

Made it a bit simplier. Improved readability.

edited Oct 19, 2018 at 23:03

13.2k
4
39
55

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO

   ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK   ( ( 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 ) = 1 ) GO

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO

 ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( ( 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 ) = 1 ) GO

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO   ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK   ( ( 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 ) = 1 ) GO

Made it a bit simplier. Improved readability.

Source Link

edit approved Oct 19, 2018 at 23:03

Community Bot

1

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO

ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK (  ( CASE WHEN col1 IS NOT NULL THEN 10 ELSE 01 END + CASE WHEN col2 IS NOT NULL THEN 10 ELSE 01 END + CASE WHEN col3 IS NOT NULL THEN 10 ELSE 01 END)   ) = 1 ) GO

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO   ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END) = 1 ) GO

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO

ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK (  ( 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   ) = 1 ) GO

deleted 59 characters in body

Source Link

edited Jun 26, 2015 at 12:35

Mark Storey-Smith

31.9k
9
91
125

Edit: Update following correction to original question.

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END) = 1 ) GO

Edit: Update following correction to original question.

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END) = 1 ) GO

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL); GO ALTER TABLE MyTable ADD CONSTRAINT CheckOnlyOneColumnIsNull CHECK ( (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END) = 1 ) GO

deleted 345 characters in body

Source Link

edited Sep 1, 2011 at 13:40

Mark Storey-Smith

31.9k
9
91
125

Loading

Source Link

answered Sep 1, 2011 at 1:19

Mark Storey-Smith

31.9k
9
91
125

Loading

Stack Exchange Network

Return to Answer