CODEWITHSUNDEEP
sqldatabaseoracle-databaseconstraintsunique-constraint
the_overflowing_stack
the_overflowing_stack

Reputation: 261

Nullable compound primary key

I want to be able to do the following:

insert into myTable values ('myParamName', NULL, 'alskdjflas');
insert into myTable values ('myParamName', 'A', 'asdf');

BUT it needs to give an error when I run this after:

insert into myTable values ('myParamName', 'A', 'asdf2');

I also need to be able to do this:

select * 
from parameters p 
left join myTable mt on mt.parameter_id = p.parameter_id 
                     and mt.visible_mask_substring = NULL

And this:

select * 
from parameters p 
left join myTable mt on mt.parameter_id = p.parameter_id 
                     and mt.visible_mask_substring = 'A'

So I need to be able to LEFT JOIN on the second column, but this cannot be done without making the second column a primary key, but it can't be a primary key because primary keys can't be null, but my data needs to have an optionally null 2nd column.

I am about a minute away from giving up and just inserting the word "NULL" in quotes simply so I can have the second column be a PK but allow it to be nulled (likely not a proper solution):

 DROP TABLE myTable;

 CREATE TABLE myTable 
 (
    parameter_id varchar2(40),
    visible_mask_substring varchar2(1),
    someVal varchar2(50),

    PRIMARY KEY (parameter_id, visible_mask_substring),
    CONSTRAINT idek_ibfk_1 
        FOREIGN KEY (parameter_id) REFERENCES parameters (parameter_id)
 );

 insert into myTable values ('myParamName', 'NULL', 'alskdjflas');
 insert into myTable values ('myParamName', 'A', 'asdf');

Upvotes: 1

Views: 55

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

A primary key is after all just a unique constraint on certain not nullable columns in the table. You want a unique key on the columns parameter_id and visible_mask_substring. But unlike is common in SQL, you want to treat NULL in visible_mask_substring as if it were just another particular value.

As visible_mask_substring can contain any possible string, it is technically not possible to have a unique index on just these two columns and treating NULL as an additional value.

There are two solutions, both using function indexes:

If you know a value that will never ever be used, use that to represent NULL. E.g. the string '###NULL###'. The index would look like

create index idx on mytable 
(
  parameter_id,
  nvl(visible_mask_substring, '###NULL###')
);

If you cannot guarantee that any value will never be used (or if you just want a clear universal solution), use three columns:

create unique index idx on mytable 
(
  parameter_id,
  nvl(visible_mask_substring, 'NULL'),
  decode(visible_mask_substring, null, 'NULL', 'NOTNULL')
);

Demo: https://dbfiddle.uk/8HwR8Hox

Upvotes: 1

Related Questions