oracle 多列的唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1841388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unique constraint on multiple columns
提问by Nicks
I am using an oracle table and have created a unique constraint over four columns. Can these columns within the constraint have NULL in them?
我正在使用一个 oracle 表并在四列上创建了一个唯一约束。约束中的这些列可以包含 NULL 吗？
回答by Vincent Malgrat
you can have NULLs in your columns unless the columns are specified NOT NULL. You will be able to store only one instance of NULLs however (no two sets of same columns will be allowed unless all columns are NULL) :
除非将列指定为 NOT NULL，否则您的列中可以包含 NULL。但是，您将只能存储一个 NULL 实例（除非所有列都是 NULL，否则不允许有两组相同的列）：
SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);
Table created
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);
Table altered
SQL> INSERT INTO t VALUES (1, NULL);
1 row inserted
SQL> INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (1, NULL)
ORA-00001: unique constraint (VNZ.U_T) violated
SQL> /* you can insert two sets of NULL, NULL however */
SQL> INSERT INTO t VALUES (NULL, NULL);
1 row inserted
SQL> INSERT INTO t VALUES (NULL, NULL);
1 row inserted
回答by shoover
Yes, Oracle allows UNIQUE constraints to contain columns with NULL contents, but PRIMARY KEY constraints cannot contain columns containing NULL values. (Edited: was "... nullable columns...", but my example below shows that not to be true. Columns in a PK can be defined as nullable, but cannot contain NULL.)
是的，Oracle 允许 UNIQUE 约束包含具有 NULL 内容的列，但 PRIMARY KEY 约束不能包含包含 NULL 值的列。（编辑：是“...可为空的列...”，但我下面的示例表明这不是真的。PK 中的列可以定义为可为空的，但不能包含 NULL。）
You cannot have a UNIQUE constraint and a PRIMARY KEY constraint with the same columns.
不能有具有相同列的 UNIQUE 约束和 PRIMARY KEY 约束。
SQL> create table stest (col1 integer not null, col2 integer null);
Table created.
SQL> alter table stest add constraint stest_uq unique (col1, col2);
Table altered.
SQL> insert into stest values (1, 3);
1 row created.
SQL> insert into stest values (1, null);
1 row created.
SQL> insert into stest values (1, null);
insert into stest values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SUSAN_INT.STEST_UQ) violated
SQL> insert into stest values (2, null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from stest;
COL1 COL2
---------- ----------
1 3
1
2
SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2);
alter table stest add constraint stest_pk PRIMARY KEY (col1, col2)
*
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL
SQL> truncate table stest;
Table truncated.
SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2);
alter table stest add constraint stest_pk PRIMARY KEY (col1, col2)
*
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table
SQL> alter table stest drop constraint stest_uq;
Table altered.
SQL> alter table stest add constraint stest_pk PRIMARY KEY (col1, col2);
Table altered.
回答by Amber Shah
Two nulls are considered not equal in Oracle, so these columns can have nulls in them.
在 Oracle 中，两个空值被认为是不相等的，因此这些列中可以有空值。
