Oracle 分区表上具有局部索引的可空列的唯一约束强制

是否有本地vs.全局索引替代方案，以便在转换未分区到列表分区表时对可为空的列强制唯一约束？在我的示例中，我使用了一个基于函数的全局唯一索引hr.per_ora_uk ，以在一个活动中实现oracle_id的唯一性。使用本地索引对NOT NULL列强制此操作没有问题。一旦我有两个NULL oracle_id ，本地索引的唯一性就会成为一个问题。

我倾向于避免全局索引，因为这些是不同的客户，如果可能，我希望保留本地索引的所有好处。

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

要创建本地唯一索引，其列之一必须是分区键。上面没有功能！

要允许多个空值，需要添加一些内容，使oracle_id为空值时索引条目唯一。幸运的是你已经有了这个： user_sa_id!

因此，您所需要做的就是创建一个索引：

activity_sa_id,nvl(oracle_id, user_sa_id)

您可以创建本地索引：

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