Geek Answers Handbook

How to have a combination of primary keys that can have null values?

I have two tables A and B as follows.

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

Column A_2 in table A can sometimes be zero, but the combination of A_1 and A_2 is always unique. I need A_2 to be part of the primary key, because then only I can refer to A_1 and A_2 as foreign keys in table B. The problem here in the primary key cannot be null. How to solve this problem? Any answer would be much appreciated

+6
oracle constraints
source share
3 answers

You solve this problem without having it as a primary key. Primary keys cannot be NULL or, if they are composite primary keys, cannot contain NULL . Make it a unique index. Create an autodial field for the primary key.

+21
source share

You cannot have a null column in the primary key, but you can create a Unique index with null columns. To make this work in Oracle 10g, I also had to explicitly add a unique column to 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 to check this setting, and it does not work as I expected. For example:

 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's what is expected. There is no row in the parent, so the row should not be allowed in the child table:

 SQL> insert into b1 values (2, null, 1); 1 row created.

It seems that just let this row be inserted without fail, although in t1 there are no rows with 2, zero 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 behavior, since the unique index on t1 behaves as you would expect (only 1 row can be inserted with 1, zero, etc.).

+2
source share

If you use "deferred initial deferral" on the primary key, you can have NULL values ​​...

+1
source share

More articles:


All Articles