Bug 6655

Summary: Disallow creating duplicate unique constraints and associated storage on columns which already have a unique or pkey constraint defined
Product: SQL Reporter: Martin van Dinther <martin.van.dinther>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: normal    
Priority: Normal    
Version: 11.31.11 (Aug2018-SP1)   
Hardware: All   
OS: All   

Description Martin van Dinther cwiconfidential 2018-11-01 15:25:48 CET
User-Agent:       Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:63.0) Gecko/20100101 Firefox/63.0
Build Identifier: 

Currently it is allowed to create multiple unique constraints on the same column (or column combination) of tables when the column (or column combination) already contains a unique or pkey constraint.
Those duplicate unique constraints are not useful.
Also each unique constraint creates an additional persistent storage structure (of oids) which also needs to be updated each time a row is added, updated or removed form the table.
Therefore duplicate unique constraints shouldn't be allowed to be created (via CREATE TABLE ...) or added (via ALTER TABLE ... ADD CONSTRAINT ... UNIQUE | PRIMARY KEY).
Idem for duplicate unique indexes (although they currently do not create persistent storage structures). They also shouldn't be allowed to be created (via CREATE UNIQUE INDEX ...) when the column or column combination already has a pkey or unique constraint.

Reproducible: Always

Steps to Reproduce:
create table mkey6655 (id int not null primary key, nm varchar(99) not null);
insert into mkey6655 values (1, 'one');
insert into mkey6655 values (2, 'two');
insert into mkey6655 values (3, 'free');
select * from mkey6655;

alter table mkey6655 add constraint mkey_pk2 primary key (id);
-- Will report: Error: CONSTRAINT PRIMARY KEY: a table can have only one PRIMARY KEY

alter table mkey6655 add constraint uc_id_1 unique (id);
-- is accepted although there is already a unique primary key defined on this column, so it should be rejected
alter table mkey6655 add constraint uc_id_2 unique (id);
-- is accepted although there is already a unique constraint defined on this column, so it should be rejected
alter table mkey6655 add constraint uc_id_3 unique (id);
-- is accepted although there is already a unique constraint defined on this column, so it should be rejected

alter table mkey6655 add constraint uc_id_nm_1 unique (id, nm);
alter table mkey6655 add constraint uc_id_nm_2 unique (id, nm);
-- is accepted although there is already a unique constraint defined on this column-list, so it should be rejected
alter table mkey6655 add constraint uc_id_nm_3 unique (id, nm);
-- is accepted although there is already a unique constraint defined on this column-list, so it should be rejected

select * from storage('sys', 'mkey6655');
 -- strangely the count and columnsize of mkey6655_id_pkey is 0 !!

insert into mkey6655 values (4, 'four');
select * from storage('sys', 'mkey6655');
-- strangely the count and columnsize of uc_id_1, uc_id_2 and uc_id_3 are *not* updated !!

delete from mkey6655 where id = 2;
delete from mkey6655 where id = 3;
select * from storage('sys', 'mkey6655');
-- strangely the count and columnsize of uc_id_1, uc_id_2 and uc_id_3 are *not* updated !!

drop table mkey6655;
select * from storage('sys', 'mkey6655');

Actual Results:  
The creations of constraints uc_id_1, uc_id_2, uc_id_3, uc_id_nm_2 and uc_id_nm_3 are allowed, creating duplicate storage structures.


Expected Results:  
The creations of constraints uc_id_1, uc_id_2, uc_id_3, uc_id_nm_2 and uc_id_nm_3 should result in an error "column id already has a unique constraint defined" or "column id, nm already have a unique constraint defined".
Comment 1 Martin van Dinther cwiconfidential 2018-11-01 15:42:16 CET
Extend test also with:

create unique index mkey6655_uix_1 on mkey6655 (id);
-- is accepted although there is already a unique primary key defined on this column, so it should be rejected
create unique index mkey6655_uix_2 on mkey6655 (id);
-- is accepted although there is already a unique constraint defined on this column, so it should be rejected
create unique index mkey6655_uix_3 on mkey6655 (id);
-- is accepted although there is already a unique constraint defined on this column, so it should be rejected

create unique index mkey6655_uix_4 on mkey6655 (id, nm);
create unique index mkey6655_uix_5 on mkey6655 (id, nm);
-- is accepted although there is already a unique constraint defined on this column-list, so it should be rejected
create unique index mkey6655_uix_6 on mkey6655 (id, nm);
-- is accepted although there is already a unique constraint defined on this column-list, so it should be rejected

create unique index mkey6655_uix_7 on mkey6655 (nm, id);
create unique index mkey6655_uix_8 on mkey6655 (nm, id);
-- is accepted although there is already a unique index defined on this column-list, so it should be rejected
create unique index mkey6655_uix_9 on mkey6655 (nm, id);
-- is accepted although there is already a unique index defined on this column-list, so it should be rejected

select * from storage('sys', 'mkey6655');
-- apparently the unique indexes also report storage columns !!