How to have a primary key combination that may have null values?

Question

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_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

Solution 1

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.

Solution 2

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:

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!

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

Solution 3

If you use "deferrable initially deferred" on the primary key, you can HAVE NULL values...