AWG Blogs

  • Getting ADB Working for SPH-M840 - Had a SPH-M840 Galaxy Ring Virgin Mobile 3G Android version 4.1.2, attempting to install apps from Android Studio failed to detect device. Installed SAMSUN...
    4 months ago
  • How to check if I have write permissions to an Oracle table - SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END AS PERMISSIONS FROM ( SELECT privilege FROM ( select * from dba_tab_privs where (grantee = 'MY_USE...
    4 months ago
  • JSF vs Struts 2 - JSF was designed in part by the authors of Struts to create a "Struts" that did a more accurate implementation of MVC (Struts is technically "Model 2") a...
    5 months ago
  • XML Beans vs JAXB - Seems XML Beans is superior: JAXB provides support for the XML schema specification, but handles only a subset of it; XMLBeans supports all of it. Also, by...
    9 months ago
  • Algorithms and OOP - In addition to DCI, "generic programming" as well as the move to functional programming appears to add nuance to the OOP notion of joining behavior with da...
    11 months ago
  • Flyweight vs Singleton - Implementations seems to be virtually identical, differing only in style, where the flyweight object is created and held by associated objects (containers:...
    1 year ago
  • init-param vs context-param - see http://javahash.com/difference-between-servlet-init-and-context-parameter/ for background. Gist: context-param variables are global and accessible thro...
    1 year ago
  • rbenv vs RVM - RVM is responsible not only for changing Ruby versions, but for installing rubies and managing gemsets, as well. ...Along with rbenv [to manage ruby versi...
    1 year ago

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