oracle 如何拥有可能具有空值的主键组合？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2415928/
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
How to have a primary key combination that may have null values?
提问by Ram
I have two tables A and B as defined bellow.
我有两个表 A 和 B，如下定义。
create table A
(
A_1 varchar2(10) NOT NULL,
A_2 varchar2(10),
A_3 varchar2(10),
constraint A_PK primary key (A_1,A_2)
)
TABLE A DATA
A_1 |A_2 |A_3
1111 abc some_text1
1111 null some_text1
1112 abc some_text2
1113 def some_text3
create table B
(
B_1 varchar2(10) NOT NULL,
B_2 varchar2(10),
B_3 varchar2(10),
constraint B_PK primary key (B_1,B_2,B_3),
constraint B_FK foreign key (B_1,B2) references A(A_1,A_2)
)
TABLE B DATA
B_1 | B_2 |B_3
1111 abc text1
1111 null text2
1111 null text3
1111 null text4
A_2 column in table A can sometimes be null but the combination of A_1 and A_2 is always unique. I need A_2 be part of primary key because then only I can reference A_1 and A_2 them as foreign keys in table B. Problem here is primary key can not be null. How to solve this problem? Any response will be highly appreciated
表 A 中的 A_2 列有时可以为空，但 A_1 和 A_2 的组合始终是唯一的。我需要 A_2 成为主键的一部分，因为只有我才能将 A_1 和 A_2 引用为表 B 中的外键。这里的问题是主键不能为空。如何解决这个问题呢？任何回应将不胜感激
回答by cletus
You solve this problem by not having this as a primary key. Primary keys cannot be NULLor, if they're composite primary keys, cannot contain NULL. Make it a unique index instead. Create an autonumber field for the primary key.
您可以通过不将其作为主键来解决此问题。主键不能是，NULL或者，如果它们是复合主键，则不能包含NULL. 改为使其成为唯一索引。为主键创建一个自动编号字段。
回答by Stephen ODonnell
You cannot have a null column in a primary key, but you can create a Unique index with null columns instead. To get this to work in Oracle 10g, I also had to explicitly add a unique contraint on the table:
主键中不能有空列，但可以改为使用空列创建唯一索引。为了让它在 Oracle 10g 中工作，我还必须在表上显式添加一个独特的约束：
create table t1 (a1 integer not null,
a2 integer,
a3 integer);
create unique index t1_uk1 on t1(a1, a2);
alter table t1 add constraint t1_cuk1 unique (a1, a2);
create table b1 (b1 integer not null, b2 integer, b3 integer);
create index b1_idx1 on b1 (b1, b2);
alter table b1 add constraint b1_fk1
foreign key (b1, b2) references t1 (a1, a2);
However, I tried testing this setup out, and it doesn't work like I expected it would. For instance:
但是，我尝试测试此设置，但它并没有像我预期的那样工作。例如：
SQL> insert into t1 values (1, null, 1);
1 row created.
SQL> insert into b1 values (1, 1, 1);
insert into b1 values (1, 1, 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (B1_FK1) violated - parent key not
found
OK, so that is what is expected. No row in parent, so a row should not be allowed in the child table, however:
好的，这就是预期的结果。父表中没有行，因此子表中不应允许行，但是：
SQL> insert into b1 values (2, null, 1);
1 row created.
Looks like it just let that row get inserted without failing, even though there are no rows in t1 with 2, null at all!
看起来它只是让该行插入而不会失败，即使 t1 中没有带有 2、null 的行！
SQL> commit;
Commit complete.
SQL> select * from t1;
A1 A2 A3
---------- ---------- ----------
1 1
SQL> select * from b1;
B1 B2 B3
---------- ---------- ----------
2 1
I was surprised by this behaviour, as the unique index on t1 behaves as you would expect it to (only 1 row can be inserted with 1, null etc).
我对这种行为感到惊讶，因为 t1 上的唯一索引的行为与您期望的一样（只有 1 行可以插入 1、null 等）。
回答by xnagyg
If you use "deferrable initially deferred" on the primary key, you can HAVE NULL values...
如果在主键上使用“可延迟初始延迟”，则可以有 NULL 值...
