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...
    7 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...
    7 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...
    8 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...
    1 year 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...
    1 year 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 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...
    2 years 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';



(paste the below rollback commands in sql developer worksheet run as script and click commit)

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

No comments:

Post a Comment