Oracle PL/SQL/Constraints/Unique

Add unique constraints

   
SQL>
SQL>
SQL> set echo off
SQL> create table emp(
  2           emp_id            integer     primary key using index (create index pk_idx on emp(emp_id) )
  3          ,lastname               varchar2(20)   constraint lastname_create_nn not null
  4          ,firstname              varchar2(15)   constraint firstname_create_nn not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode           varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50)
 14          ,constraint unique_emp_phone unique(phone) using index (create index emp_phone_u_idx on emp(phone))
 15          );
Table created.
SQL>
SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "emp";
no rows selected
SQL>
SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "emp";
no rows selected
SQL>
SQL> drop table emp             cascade constraints;
Table dropped.

Add unique containt to a varchar2 type column

   
SQL>
SQL> create table inventory(
  2  partno number(4) constraint invent_partno_pk primary key,
  3  partdesc varchar2(35) constraint invent_partdesc_uq unique);
Table created.
SQL>
SQL>
SQL>
SQL> drop table inventory;
Table dropped.
SQL>

A unique constraint can be extended over multiple columns

  

SQL> -- A unique constraint can be extended over multiple columns:
SQL>
SQL> create table myTable (
  2    a number,
  3    b number,
  4    c number,
  5    unique (a,b)
  6  );
Table created.
SQL>
SQL> insert into myTable values (4, 3,  5);
1 row created.
SQL> insert into myTable values (4, 1,  5);
1 row created.
SQL> insert into myTable values (4, 2,  5);
1 row created.
SQL> insert into myTable values (4, 3,  5);
insert into myTable values (4, 3,  5)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C004360) violated

SQL>
SQL> select * from myTable;
         A          B          C
---------- ---------- ----------
         4          3          5
         4          1          5
         4          2          5
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Create a table with "unique deferrable initially immediate"

   
SQL>
SQL>
SQL> create table inventory(
  2  partno number(4) constraint partno_pk primary key deferrable initially immediate,
  3  partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate);
Table created.
SQL>
SQL>
SQL> drop table inventory;
Table dropped.
SQL>
SQL>
SQL>

if a column is not explicitely defined as not null, nulls can be inserted multiple times

  

SQL> --Unique Key
SQL>
SQL>
SQL> create table myTable (
  2    a number unique,
  3    b number
  4  );
Table created.
SQL>
SQL> desc myTable;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 A                                                                                                      NUMBER
 B                                                                                                      NUMBER
SQL>
SQL> -- if a column is not explicitely defined as not null, nulls can be inserted multiple times:
SQL>
SQL> insert into myTable values (4,   5);
1 row created.
SQL> insert into myTable values (4,   1);
insert into myTable values (4,   1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C004353) violated

SQL> insert into myTable values (9,   8);
1 row created.
SQL> insert into myTable values (6,   9);
1 row created.
SQL> insert into myTable values (null,9);
1 row created.
SQL> insert into myTable values (null,9);
1 row created.
SQL>
SQL> select * from myTable;
         A          B
---------- ----------
         4          5
         9          8
         6          9
                    9
                    9
SQL>
SQL> drop table myTable;

ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated

   
SQL> create table emp
  2  (emp_ID number primary key,
  3   teamid number,
  4   job varchar2(100),
  5   status varchar2(20) check (status in ("ACTIVE", "INACTIVE"))
  6  );
Table created.
SQL>
SQL>
SQL> create UNIQUE index job_unique_in_teamid on emp
  2  ( case when status = "ACTIVE" then teamid else null end,
  3    case when status = "ACTIVE" then job    else null end
  4  )
  5  /
Index created.
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 1, 10, "a", "ACTIVE" );
1 row created.
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" );
insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" )
*
ERROR at line 1:
ORA-00001: unique constraint (sqle.JOB_UNIQUE_IN_TEAMID) violated

SQL>
SQL>
SQL> update emp
  2     set status = "INACTIVE"
  3    where emp_id = 1
  4      and teamid = 10
  5      and status = "ACTIVE";
1 row updated.
SQL>
SQL>
SQL> insert into emp(emp_id,teamid,job,status)values( 2, 10, "a", "ACTIVE" );
1 row created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Setting a Unique Constraint

   
SQL>
SQL>
SQL> CREATE TABLE myTable (
  2     Name    VARCHAR(50) NOT NULL,
  3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
  4     CONSTRAINT MyUniqueKey UNIQUE (Name)
  5  );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Unique value column

   
SQL>
SQL>
SQL>
SQL> create table t  ( x int unique );
Table created.
SQL> insert into t values ( 1 );
1 row created.
SQL> insert into t values ( 2 );
1 row created.
SQL> update t set x = x+1;
2 rows updated.
SQL>
SQL> drop table t;
Table dropped.
SQL>

Vialate the unique contraint: try to insert the same value

  

SQL> --Unique Key
SQL>
SQL>
SQL> create table myTable (
  2    a number unique,
  3    b number
  4  );
Table created.
SQL>
SQL> desc myTable;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 A                                                                                                      NUMBER
 B                                                                                                      NUMBER
SQL>
SQL> insert into myTable values (4,   5);
1 row created.
SQL> insert into myTable values (2,   1);
1 row created.
SQL> insert into myTable values (2,   1); -- Dup
  2  insert into myTable values (9,   8);
insert into myTable values (2,   1); -- Dup
                                   *
ERROR at line 1:
ORA-00911: invalid character

SQL> insert into myTable values (6,   9);
1 row created.
SQL> insert into myTable values (null,9);
1 row created.
SQL> insert into myTable values (null,9);
1 row created.
SQL>
SQL> select * from myTable;
         A          B
---------- ----------
         4          5
         2          1
         6          9
                    9
                    9
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
SQL>