Mega Code Archive
Getting Information on Constraints
<![CDATA[
You can get information on your constraints by querying user_constraints.
SQL> desc user_constraints;
Name Null? Type
--------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1) --Constraint type. Set to P, R, C, U, V, or O.
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8) --Constraint status. Set to ENABLED or DISABLED.
DEFERRABLE VARCHAR2(14) --Whether the constraint is deferrable. Set to DEFERRABLE or NOT DEFERRABLE.
DEFERRED VARCHAR2(9) --Whether the deferred. Set to IMMEDIATE or DEFERRED.
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
SQL>
SQL> -- create demo table
SQL> create table myTable(
2 id NUMBER(2),
3 value NUMBER(6,2)
4 )
5 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created.
SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created.
SQL>
SQL> select * from myTable
2 /
ID VALUE
---------- ----------
1 9
2 2.11
3 3.44
4 -4.21
5 10
6 3
7 -5.88
8 123.45
9 98.23
9 rows selected.
SQL>
SQL> ALTER TABLE myTable
2 ADD CONSTRAINT uq UNIQUE (id)
3 DEFERRABLE INITIALLY DEFERRED;
Table altered.
SQL>
SQL> SELECT
2 constraint_name, constraint_type, status, deferrable, deferred
3 FROM user_constraints
4 WHERE table_name = 'MYTABLE';
CONSTRAINT_NAME C STATUS DEFERRABLE DEFERRED
------------------------------ - -------- -------------- ---------
UQ U ENABLED DEFERRABLE DEFERRED
SQL>
SQL> -- clean the table
SQL> drop table myTable
2 /
Table dropped.
SQL>
]]>