如何拥有可能具有空值的主键组合? [英] How to have a primary key combination that may have null values?

问题描述

我在下面定义了两个表A和B.

I have two tables A and B as defined bellow.

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中的

A_2列有时可以为空，但是A_1和A_2的组合始终是唯一的.我需要A_2作为主键的一部分，因为只有这样才能将A_1和A_2引用为表B中的外键.这里的问题是主键不能为null.如何解决这个问题呢? 任何回应将不胜感激

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

推荐答案

您可以通过不将其作为主键来解决此问题.主键不能为NULL，或者如果它们是复合主键，则不能包含NULL.而是使其成为唯一索引.为主键创建一个自动编号字段.

You solve this problem by not having this as a primary key. Primary keys cannot be NULL or, if they're composite primary keys, cannot contain NULL. Make it a unique index instead. Create an autonumber field for the primary key.

这篇关于如何拥有可能具有空值的主键组合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！