Saturday, October 19, 2013

Constraints (Part 3) - More About Unique Constraints


This example, tested on an Oracle 11 database, shows a different way to set up a unique constraint. As before, a table is created:

SQL> create table my_table
  2  (my_column number)
  3  /

Table created.

SQL>

Then a check is carried out to ensure there is no constraint or unique index called MY_UK:

SQL> select owner, constraint_name, constraint_type, status
  2  from dba_constraints
  3  where constraint_name = 'MY_UK'
  4  /

no rows selected

SQL> select owner, index_name, uniqueness
  2  from dba_indexes
  3  where index_name = 'MY_UK'
  4  /

no rows selected

SQL>

A constraint is added to the table:

SQL> alter table my_table
  2  add constraint my_uk
  3  unique (my_column)
  4  /

Table altered.

SQL>

This time the new constraint is included in DBA_CONSTRAINTS. Note the CONSTRAINT_TYPE of U, as this is a unique key constraint:

SQL> select owner, constraint_name, constraint_type, status
  2  from dba_constraints
  3  where constraint_name = 'MY_UK'
  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
---------- --------------- --------------- --------
ANDREW     MY_UK           U               ENABLED

SQL>

There is also an index associated with the constraint:

SQL> select owner, index_name, uniqueness
  2  from dba_indexes
  3  where index_name = 'MY_UK'
  4  /

OWNER      INDEX_NAME UNIQUENESS
---------- ---------- ----------
ANDREW     MY_UK      UNIQUE

SQL>

A row is added to the table:

SQL> insert into my_table values (1)
  2  /

1 row created.

SQL>

And the unique constraint can then be checked by attempting to add the same row again:

SQL> insert into my_table values (1)
  2  /
insert into my_table values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL> select * from my_table
  2  /

 MY_COLUMN
----------
         1

The constraint is disabled:

SQL> alter table my_table
  2  disable constraint my_uk
  3  /

Table altered.

SQL>

This changes its status:

SQL> select owner, constraint_name, constraint_type, status
  2  from dba_constraints
  3  where constraint_name = 'MY_UK'
  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
---------- --------------- --------------- --------
ANDREW     MY_UK           U               DISABLED

SQL>

And the index disappears:

SQL> select owner, index_name, uniqueness
  2  from dba_indexes
  3  where index_name = 'MY_UK'
  4  /

no rows selected

SQL>

It is then possible to add duplicate data to the table:

SQL> insert into my_table values (1)
  2  /

1 row created.

SQL> select * from my_table
  2  /

 MY_COLUMN
----------
         1
         1

No comments: