Constraints

The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

Integrity constraints are used to ensure accuracy and consistency of data in relational data base 

Two types of constraints exists

1.Column level constraints 

2.Table level constraints

1.COLUMN LEVEL CONSTRAINTS :

They are imposed on single column ,the constraints are applied while table creation and they can not be applied if a constraint spans more than one column in a table

Syntax :

Column name datatype <constraint_name>   

Example:

SQL> create table std(sno number(2) primary key,sname varchar(11));

2.TABLE LEVEL CONSTRAINTS :

These are applied on more than one column in a table.

Syntax :

Constraint  [<constraint_name>] <constraint_types>

Example on single column:

SQL> create table std(sno number(2),sname varchar(11),primary key(sno));

OR

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno));

Example on more than one column:

SQL> create table std(sno number(2),sname varchar(11),primary key(sno,sname));

                                                    OR

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno,sname));

TYPES OF CONSTRAINTS :

• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY  

NOT NULL:

NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values.

Example:

SQL> create table abc(sno number(2) not null,sname varchar(11));
Table created.
SQL> insert into abc values(11,'raj');
1 row created.
SQL> insert into abc(sname) values('raj');
insert into abc(sname) values('raj')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."ABC"."SNO")

UNIQUE:

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.

Example:

SQL> create table abc(sno number(2) unique,sname varchar(11));
Table created.
SQL> insert into abc values(11,'raj');
1 row created.
SQL> insert into abc values(11,'raj');
insert into abc values(11,'raj')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004058) violated

DEFAULT:

The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.

Example :

SQL> create table abc(sno number(2) unique,sname varchar(22),city varchar(11) default('vsp'));
Table created.
SQL> insert into abc values(11,'raj','hyd');
1 row created.
SQL> insert into abc values(11,'raj',default);
insert into abc values(11,'raj',default)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004060) violated

SQL> insert into abc values(12,'raj',default);
1 row created.
SQL> insert into abc(sno,sname) values(13,'rasdf');
1 row created.
SQL> select * from abc;
       SNO SNAME                  CITY
---------- ---------------------- -----------
        11 raj                    hyd
        12 raj                    vsp
        13 rasdf                 vsp

CHECK:

This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.

Example :

SQL> create table abc(sno number(2) unique,sname varchar(22),city varchar(11) check(city in('vsp','hyd')));
Table created.
SQL> insert into abc values(11,'raj','vsp');
1 row created.
SQL> insert into abc values(22,'raj','vizag');
insert into abc values(22,'raj','vizag')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C004061) violated

Key Constraints – PRIMARY KEY, FOREIGN KEY

1.Primary key :A key that is used for unique identification of each row in a table is known as primary key of the database.

Example :

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno));
Table created.
SQL> insert into std values(11,'raju');
1 row created.
SQL> insert into std values(22,'rani');
1 row created.
SQL> select * from std;
       SNO SNAME
---------- -----------
        11 raju
        22 rani
SQL> insert into std values(11,'ramesh');
insert into std values(11,'ramesh')
*
ERROR at line 1:
ORA-00001: unique constraint (RK.PK_STD) violated

2. Foreign  Key :Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

Example :

SQL> create table std(sno number(2),sname varchar(11),course varchar(7),cid number(3),primary key(sno));

SQL> select * from std;

       SNO SNAME       COURSE         CID
---------- ----------- ------- ----------
        11 raju           mpc           520
        22 rani           bipc           420
        33 suresh       mpc           520
        44 ramesh      bipc           420
        55 sita            mpc          520

SQL> Create table faculty (fid number(3)  primary key, fname varchar2(15),  sno number(2), foreign key (sno) references  std(sno));

SQL> select * from faculty;
       FID FNAME                  SNO
---------- --------------- ----------
       111 rk                      11
       222 pk                      44

Relational set operators based on the above 2 tables ( std and faculty):

SQL> select sno from std union select sno from faculty ;

       SNO
----------
        11
        22
        33    
        44
        55

SQL> select sno from std union all select sno from faculty ;

       SNO

----------

        11

        22

        33

        44

        55

        11

        44

7 rows selected.

SQL> select sno from std intersect select sno from faculty ;

       SNO

----------

        11

        44

SQL> select sno from std minus select sno from faculty ;

       SNO

----------

        22

        33

        55

SQL> select sno from faculty minus select sno from std ;

no rows selected