Oracle PL/SQL/Constraints/Contraint Name

Add constraint to a column and give it a name

   <source lang="sql">
 

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.

 </source>
   
  

Name the constraint

   <source lang="sql">

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>

 </source>
   
  

SYS.SYS_C004353 is the contraint name

   <source lang="sql">

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;

 </source>