Sql server: create table with column that support unique and multiple nulls

Question

1.00/5 (1 vote)

See more:

SQL server 2014/16.

Want create a table. The table has some columns that need support:
1.Can contain multiple nulls.
2.If not null, then must be unique.
3.The columns has no relationship.
4.Not entire row is unique. Each column is unique.

How to make the CREATE TABLE command?

It seems SQL server can only support one null when using UNIQUE.

Below example is what I wanted:
CREATE TABLE UniqueTest (
col1 int,
col2 int unique null
);

INSERT INTO UniqueTest VALUES (1, 1);
-- SUCCESS

INSERT INTO UniqueTest VALUES (2, 2);
-- SUCCESS

INSERT INTO UniqueTest VALUES (3, 2);
-- FAIL

INSERT INTO UniqueTest VALUES (4, NULL);
-- SUCCESS

INSERT INTO UniqueTest VALUES (5, NULL);
-- SUCCESS

I had searched internet, lots of articles are discussed in old SQL server version, e.g. 2005/2008.
I think the SQL server 2014/2016 has a new CREATE TABLE option to meet my requirement, but I don't know.

What I have tried:

The solution from internet would be:
create index on specific column.

But:
1.My table will has 10~30 columns that need unique and nulls. If each column create an index, then will be 10~30 indexes in a table. Is it possible?
2.Lots indexes will lower efficiency. Is it?

Posted 23-May-17 18:13pm

w14243

Updated 24-May-17 3:05am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2017-05-23T18:30:00

If I understand the question correctly you could use an unique index to enforce the logic.

Consider the following test

SQL

CREATE TABLE UniqueTest (
   col1 int,
   col2 int
);

CREATE UNIQUE INDEX U_UniqueTest ON UniqueTest (col1, col2);

-- Test variations

INSERT INTO UniqueTest VALUES (1, 1);
-- SUCCESS

INSERT INTO UniqueTest VALUES (1, 2);
-- SUCCESS

INSERT INTO UniqueTest VALUES (1, NULL);
-- SUCCESS

INSERT INTO UniqueTest VALUES (NULL, 1);
-- SUCCESS

INSERT INTO UniqueTest VALUES (NULL, NULL);
-- SUCCESS

-- RE-RUN
INSERT INTO UniqueTest VALUES (1, 1);
-- Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'U_UniqueTest'. The duplicate key value is (1, 1).

INSERT INTO UniqueTest VALUES (1, 2);
-- Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'U_UniqueTest'. The duplicate key value is (1, 2).

INSERT INTO UniqueTest VALUES (1, NULL);
-- Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'U_UniqueTest'. The duplicate key value is (1, <NULL>).

INSERT INTO UniqueTest VALUES (NULL, 1);
-- Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'U_UniqueTest'. The duplicate key value is (<NULL>, 1).

INSERT INTO UniqueTest VALUES (NULL, NULL);
-- Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'U_UniqueTest'. The duplicate key value is (<NULL>, <NULL>).