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