AWG Blogs

Wednesday, May 31, 2017

Creating a multi-column constraint where one column has nulls

To create a multi-column unique constraint on a table where the new column will have nulls initially
need to ensure that the index associated with the unique constraint is non-unique. This can be done
using 'DEFERRABLE' in the constraint command or by creating a non-unique index first and
referring to it with a USING INDEX __ in the constraint command:

select name from v$database;

Alter table MYSCHEMA.MY_TABLE add MY_COLUMN  varchar2(250);

comment on column MY_TABLE.MY_COLUMN is 'a column description';

ALTER TABLE MYSCHEMA.MY_TABLE ADD CONSTRAINT XYZ1_MY_TABLE UNIQUE (ANOTHER_COLUMN, MY_COLUMN) DEFERRABLE NOVALIDATE;

commit;

-- ROLLBACK
(paste the below rollback commands in sql developer worksheet run as script and click commit)
ALTER TABLE MYSCHEMA.MY_TABLE DROP CONSTRAINT XYZ1_MY_TABLE;

Alter table MYSCHEMA.MY_TABLE drop column MY_COLUMN;

--verify column, constraint and associated index is deleted:
select index_name,index_type, uniqueness, visibility from dba_indexes where table_name='MY_TABLE' order by index_name;

select * from dba_constraints where table_name='MY_TABLE';