Sql server：create table with column，支持唯一和多个空值 [英] Sql server: create table with column that support unique and multiple nulls

问题描述

SQL server 2014/16。



想创建一个表。该表有一些需要支持的列：

1.可以包含多个空值。

2.如果不为空，则必须是唯一的。

3.列没有任何关系。

4.整行不是唯一的。每一列都是唯一的。



如何制作CREATE TABLE命令？



似乎SQL服务器可以使用UNIQUE时只支持一个null。



以下示例是我想要的：

CREATE TABLE UniqueTest（

col1 int，

col2 int unique null

）;



INSERT INTO UniqueTest VALUES（1,1 ）;

- 成功



插入UniqueTest VALUES（2,2）;

- SUCCESS



INSERT INTO UniqueTest VALUES（3,2）;

- 失败



INSERT INTO UniqueTest VALUES（4，NULL）;

- SUCCESS



INSERT INTO UniqueTest VALUES（5，NULL）;

- 成功





我搜索了互联网，旧的SQL服务器版本讨论了很多文章，例如2005/2008。

我认为SQL Server 2014/2016有一个新的CREATE TABLE选项可以满足我的要求，但我不知道。



我尝试过：



来自互联网的解决方案是：

创建索引特定列。



但是：

1.我的表将有10~30列需要唯一和空值。如果每列创建一个索引，那么表中将有10~30个索引。有可能吗？

2.Lots索引会降低效率。是吗？

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?

推荐答案

如果我正确理解了这个问题，你可以使用唯一索引来强制执行逻辑。



考虑以下测试

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

Consider the following test

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>).

这篇关于Sql server：create table with column，支持唯一和多个空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！