Are there local vs. global index alternatives to enforcing unique constraints on nullable columns when transitioning unpartitioned to list partitioned tables? In my sample I'm resorting to a function based global unique index hr.pers_ora_uk to enfore the uniqueness of oracle_id within an activity. There is no issue using local indexes to enforce this on NOT NULL columns. Uniqueness becomes a problem with a local index as soon as I have two NULL oracle_id's.
I'd prefer to avoid global indexes since these are different customers and I want to preserve all of the benefits of local indexes if possible.
--original unpartitioned
CREATE TABLE hr.personnel
(
user_sa_id NUMBER NOT NULL,
oracle_id VARCHAR2 (30)
);
CREATE UNIQUE INDEX hr.pers_pk
ON hr.personnel (user_sa_id);
ALTER TABLE hr.personnel ADD (
CONSTRAINT pers_pk
PRIMARY KEY
( user_sa_id)
USING INDEX
ENABLE VALIDATE);
CREATE UNIQUE INDEX hr.pers_ora_uk
ON hr.personnel (oracle_id);
--to list partitioned
CREATE TABLE hr.personnel
(
activity_sa_id NUMBER NOT NULL,
user_sa_id NUMBER NOT NULL,
oracle_id VARCHAR2 (30)
)
PARTITION BY LIST (activity_sa_id)
(PARTITION personnel1 VALUES (1),
PARTITION personnel2 VALUES (2),
PARTITION personnel3 VALUES (3));
CREATE UNIQUE INDEX hr.pers_pk
ON hr.personnel (activity_sa_id, user_sa_id)
LOCAL;
ALTER TABLE hr.personnel ADD (
CONSTRAINT pers_pk
PRIMARY KEY
(activity_sa_id, user_sa_id)
USING INDEX LOCAL
ENABLE VALIDATE);
CREATE UNIQUE INDEX hr.pers_ora_uk
ON hr.personnel (oracle_id, NVL2 ("ORACLE_ID", "ACTIVITY_SA_ID", NULL));
To make a local unique index, one of its columns must be the partition key. Without a function on it!
To allow multiple nulls you need to add something to make the index entries unique when oracle_id is null. Fortunately you already have this: user_sa_id!
So all you need to do is create an index on:
activity_sa_id,nvl(oracle_id, user_sa_id)
And you can make build your local index:
CREATE TABLE personnelp
(
activity_sa_id NUMBER NOT NULL,
user_sa_id NUMBER NOT NULL,
oracle_id VARCHAR2 (30)
)
PARTITION BY LIST (activity_sa_id)
(PARTITION personnel1 VALUES (1),
PARTITION personnel2 VALUES (2),
PARTITION personnel3 VALUES (3));
CREATE UNIQUE INDEX pers_pk
ON personnelp (activity_sa_id, user_sa_id)
LOCAL;
ALTER TABLE personnelp ADD (
CONSTRAINT pers_pkp
PRIMARY KEY
(activity_sa_id, user_sa_id)
USING INDEX LOCAL
ENABLE VALIDATE);
CREATE UNIQUE INDEX pers_ora_ukp
ON personnelp (activity_sa_id,nvl(oracle_id, user_sa_id)) local;
insert into personnelp values (1, 1, '1');
insert into personnelp values (2, 1, '1');
insert into personnelp values (3, 1, '1');
insert into personnelp values (3, 2, '1');
SQL Error: ORA-00001: unique constraint (CHRIS.PERS_ORA_UKP) violated
insert into personnelp values (3, 3, null);
insert into personnelp values (3, 4, null);
select * from personnelp;
ACTIVITY_SA_ID USER_SA_ID ORACLE_ID
1 1 1
2 1 1
3 1 1
3 3
3 4