Sql server: create table with column that support unique and multiple nulls
-
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. 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?
-
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. 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?
An answer has been posted here: [Sql server: create table with column that support unique and multiple nulls](https://www.codeproject.com/Questions/1188807/Sql-server-create-table-with-column-that-support-u#answer1)
-
An answer has been posted here: [Sql server: create table with column that support unique and multiple nulls](https://www.codeproject.com/Questions/1188807/Sql-server-create-table-with-column-that-support-u#answer1)
That question can't be replied, so I post it here. From the answer, it is unique on 'col1+col2'. But my requirement is that col1 and col2 have their self unique constraint. 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
-
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. 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?
Just use a trigger and move on. You have custom logic that SQL does not support.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
Just use a trigger and move on. You have custom logic that SQL does not support.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
RyanDev wrote:
You have custom logic that SQL does not support.
Are you sure about that? ;P
Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]:
A filtered unique index can get you around the problem, like this:
CREATE UNIQUE INDEX uq_UsersAllowNulls_DisplayName on dbo.UsersAllowNulls ( DisplayName )
WHERE DisplayName IS NOT NULL;
GO
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
That question can't be replied, so I post it here. From the answer, it is unique on 'col1+col2'. But my requirement is that col1 and col2 have their self unique constraint. 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
Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]
CREATE TABLE UniqueTest (
col1 int,
col2 int null
);CREATE UNIQUE INDEX UX_UniqueTest_col2
ON UniqueTest (col2)
WHERE col2 Is Not Null;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
RyanDev wrote:
You have custom logic that SQL does not support.
Are you sure about that? ;P
Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]:
A filtered unique index can get you around the problem, like this:
CREATE UNIQUE INDEX uq_UsersAllowNulls_DisplayName on dbo.UsersAllowNulls ( DisplayName )
WHERE DisplayName IS NOT NULL;
GO
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]
CREATE TABLE UniqueTest (
col1 int,
col2 int null
);CREATE UNIQUE INDEX UX_UniqueTest_col2
ON UniqueTest (col2)
WHERE col2 Is Not Null;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Just use a trigger and move on. You have custom logic that SQL does not support.
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
MS Access,Oracle,MySQL both support unique-nulls on column, but MS SQL server don't. It is confusing. The 'CREATE UNIQUE INDEX' solution is traditional. We need a simpler, better and easier way. So I think SQL server has new keyword option to support unique-nulls column on latest version, e.g. UNIQUE_NULLS in SQL server 2014/1026. But I don't know exact settings on CREATE TABLE. Maybe anyone knows, maybe the new option is not exist at all.
-
MS Access,Oracle,MySQL both support unique-nulls on column, but MS SQL server don't. It is confusing. The 'CREATE UNIQUE INDEX' solution is traditional. We need a simpler, better and easier way. So I think SQL server has new keyword option to support unique-nulls column on latest version, e.g. UNIQUE_NULLS in SQL server 2014/1026. But I don't know exact settings on CREATE TABLE. Maybe anyone knows, maybe the new option is not exist at all.
See above, Re: Sql server: create table with column that support unique and multiple nulls - Database Discussion Boards[^]
There are two kinds of people in the world: those who can extrapolate from incomplete data. There are only 10 types of people in the world, those who understand binary and those who don't.
-
Below is my concern: 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?
1. Yes. 2. Lots of indexes will potentially decrease the performance of inserts and updates, but no more so that any other method of enforcing your requirements, and with less chance of making a mistake. A table with 30 columns might be a candidate for further normalization. But you would need to examine the data to determine that, and test what effect that might have on the performance and complexity of your queries.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
