Constraints (Part 3) - More About Unique Constraints

Go to part 2

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

SQL>

Go to part 4