Check constraint only one of three columns is non-null

Question

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other columns are NULL. What is the simplest check constraint to achieve this?

The context for this is trying to retrofit the ability to capture non-numeric results into an existing system. Adding two new columns to the table with a constraint to prevent more than one result per row was the most economical approach, not necessarily the correct one.

Update: Sorry, data type snafu. Sadly I wasn't intending the result types indicated to be interpreted as SQL Server datatypes, just generic terms, fixed now.

score 92 · Accepted Answer · edited Oct 19 '18 at 23:03

92

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

edited Oct 19 '18 at 23:03

World Wide DBA

12,667
4
33
53

answered Sep 01 '11 at 01:19

Mark Storey-Smith

31,155
6
85
122

1

This can be further generalized to "All null or not null" by using `IN`. Example: `CHECK (IIF(ConsentDate is null, 1, 0) + IIF(ConsentingUserID is null, 1, 0) + IIF(ConsentingClientIdentifiers is null, 1, 0) in (0, 3))` – Mitch Nov 17 '20 at 16:20

score 25 · Answer 2 · edited Sep 01 '11 at 17:44

25

You'll probably need to do three tests within the constraint, one test for each pair that you want to be null and one for the column that should be not null:

ALTER TABLE table
ADD CONSTRAINT CK_one_is_null
CHECK (
     (col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL)
  OR (col2 IS NOT NULL AND col1 IS NULL AND col3 IS NULL) 
  OR (col3 IS NOT NULL AND col1 IS NULL AND col2 IS NULL)
);

edited Sep 01 '11 at 17:44

Nick Chammas

14,170
17
73
119

answered Sep 01 '11 at 01:21

mrdenny

26,776
2
40
79

1

This is not so scalable, I have a table with 9 foreign keys and only one should be not null, I prefer @MarkStoreySmith 's solution – Amir Pashazadeh Aug 25 '19 at 21:27

score 5 · Answer 3 · edited Jun 07 '18 at 00:15

5

Here's a PostgreSQL solution using the built-in array functions:

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK (array_length(array_remove(ARRAY[col1::text, col2::text, col3::text], NULL), 1) = 1);

edited Jun 07 '18 at 00:15

David Clarke

1,117
2
10
17

answered Jun 06 '18 at 17:12

CrEOF

51
1
1

2

Will this be faster implementation in postgreSQL than the previously mentioned CASE or AND/OR solutions posted by Mark Storey and mrdenny respectively? – Chris Britt Jun 12 '18 at 13:35

Mendes · Answer 4 · 2020-02-15T19:56:29.603

3

FOR POSTGRESQL

CHECK( (col_1 IS NOT NULL)::integer + (col_2 IS NOT NULL)::integer + ... = 1 )

We convert column into boolean with IS NOT NULL (true or false), then cast into ::integer (0 or 1) We can then use arithmetic operators

= 1  //must one row is not null  
<= 1 //only one row can be not null

edited Feb 15 '20 at 19:56

answered Feb 14 '20 at 19:16

Mendes

61
6

The question is about SQL Server though. Your answer will work only in Postgres. – ypercubeᵀᴹ Feb 14 '20 at 19:23
@ypercubeᵀᴹ Microsoft SQL Server. The misunderstand was caused by their chaos-marketing. – peterh Feb 14 '20 at 19:50
@peterh-ReinstateMonica not sure what you are referring to. How can one confuse PostgreSQL with SQL Server, when both products are 20+ years old? – ypercubeᵀᴹ Feb 14 '20 at 20:33
1

The answer is good by the way. Just not for this question with the specific tags. – ypercubeᵀᴹ Feb 14 '20 at 20:34
@ypercubeᵀᴹ SQL means the query language. An SQL server is a process serving SQL language requests. There are many different SQL server products, PostgreSQL is one of them. Also Microsoft has an SQL server product, they call it "SQL Server" and not "Microsoft SQL Server", which is a dirty marketing trick. Also the other answer looks PostgreSQL-ish to me a little bit, but I am not sure. – peterh Feb 14 '20 at 20:37
It probably was a dirty advertising trick 20-25 years ago. Not any more. Everyone involved in db related work knows that SQL Server and Microsoft SQL Server and MS SQL Server is the same thing. You could argue the same for "MySQL", that it was a dirty trick as well. – ypercubeᵀᴹ Feb 14 '20 at 20:40
Also many (MS SQL Server) users call it "MS SQL" (because MS used to refer to the product that way) which may sound even more dirty or more clean, depending on how you look at it ;) – ypercubeᵀᴹ Feb 14 '20 at 20:42
@ypercubeᵀᴹ No, MySQL is a name specific to a single SQL server product. Only Microsoft has chosen a general name, to create confusion. That the trick still exists and the Microsoft was not punished for that, makes the case only worsen for them. – peterh Feb 14 '20 at 20:42
@ypercubeᵀᴹ Unfortunately, also the SE follows this in the tagging, making themselfes an abettor - they have good contact with the Microsoft. All the SQL-related tags should belong to the content about the query language, and the Microsoft SQL-specific content should belong to ms-sql or ms-sql-server tags. The related initiatives on the metas were all quickly downvoted, closed and deleted. – peterh Feb 14 '20 at 20:44
You can't punish something that is not illegal. And calling something "my SQL" is a dirty trick in my opinion. If BMW made a car and called the model myCar, you would have the same opinion? – ypercubeᵀᴹ Feb 14 '20 at 20:50
Let us [continue this discussion in chat](https://chat.stackexchange.com/rooms/104488/discussion-between-ypercube-and-peterh-reinstate-monica). – ypercubeᵀᴹ Feb 14 '20 at 20:50

Check constraint only one of three columns is non-null

4 Answers4