66

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.

79

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
| improve this answer | |
24

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)
);
| improve this answer | |
  • 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
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);
| improve this answer | |
  • 1
    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
1

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
| improve this answer | |
  • The question is about SQL Server though. Your answer will work only in Postgres. – ypercubeᵀᴹ Feb 14 at 19:23
  • @ypercubeᵀᴹ Microsoft SQL Server. The misunderstand was caused by their chaos-marketing. – peterh - Reinstate Monica Feb 14 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 at 20:33
  • 1
    The answer is good by the way. Just not for this question with the specific tags. – ypercubeᵀᴹ Feb 14 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 - Reinstate Monica Feb 14 at 20:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.