Five types of Oracle Constraints

1Syntax

1.1Row-Level Definition

 

1.2Table-Level Definition

 

 

 

2NOT NULL

Row-level definition only.

SQL> create table tn0 (a number (4) not null, B varchar2 (20 ));

SQL> create table tn1 (a number (4) constraint nn_tn1_a not null, B varchar2 (20 ));

3Unique

SQL> create table tu0 (a number (4) unique, B varchar2 (20 ));

SQL> create table tu1 (a number (4) constraint u_tu1_a unique, B varchar2 (20 ));

SQL> create table tu2 (a number (4), B varchar2 (20 ),Constraint u_tu2_a unique ());

SQL> create table tu3 (a number (4), B varchar2 (20 ),Constraint u_tu3_a unique (a, B ));

4Primary Key

SQL> create table t0 (a number (4) primary key, B varchar2 (20 ));

SQL> create table t1 (a number (4), B varchar2 (20), primary key ());

SQL> create table t2 (a number (4) constraint pk_t2 primary key, B varchar2 (20 ));

SQL> create table t3 (a number (4), B varchar2 (20), constraint pk_t3 primary key ());

SQL> create table t4 (a number (4), B varchar2 (20), constraint pk_t4 primary key (a, B ));

5Foreign key

SQL> create table tf_p (a number (4) primary key, B varchar2 (20 ));

SQL> insert into tf_p values (1, 'A ');

SQL> insert into tf_p values (2, 'bb ');

SQL> insert into tf_p values (3, 'cc ');

SQL> create table tf_s (a number (4) constraint fk_tf_sa_ref_tf_pa references tf_p, c varchar2 (20 ));