Oracle PL/SQL/Constraints/Contraint Name

Add constraint to a column and give it a name

  
SQL>
SQL> create table emp_1
  2  (emp_id    number constraint pk_emp_id primary key,
  3   firstname  varchar2(15) constraint firstname_nn not null,
  4   lastname   varchar2(20) constraint lastname_nn not null,
  5   midinit    char(1) );
Table created.
SQL>
SQL> drop table emp_1;
Table dropped.

Name the constraint

 

SQL> --name the constraint.
SQL> -- creates a unique constraint on the columns a and b and names the constraint uq_myTable.
SQL>
SQL> create table myTable (
  2    a number,
  3    b number,
  4    c number,
  5    constraint uq_myTable unique (a,b)
  6  );
Table created.
SQL>
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.UQ_MYTABLE) 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>

SYS.SYS_C004353 is the contraint name

 

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;