Constraints

Constraint: Is to achieve data integrity.

Data Integrity: It is a state where all the values in the database are correct.

Classification of constraints:

1. Column level: When a constraint is applied to a single column then it is a column level constraint. These constraints will affect the values being entered for that particular column only irrespective of values of other columns.
2. Table level: when a single constraint is applied to more than one column then it is called table level constraints. These values impact the values being entered for combination of column values, for example salary can never be less than the commission is a table level constraint.

Types of constraints

1. Not Null: It prohibits the column having NULL values.

SQL> Create table student (rollno number (4), name varchar2 (10) not null);

SQL> Create table student (rollno number(4), name varchar2(10) constraint name_nn not null); // alternative way

2. Unique: It designates a column as unique key, no two rows can have same values. But it allows NULL values.

SQL> create table student (rollno number (4) unique, name varchar2 (10));

SQL> create table student (rollno number (4) constraint rollno_uq unique, name varchar2 (10)); // alternative way

3. Primary Key: Primary key can be defined as the single column or combination of columns uniquely identifies a row in a table and field can not be left NULL.

SQL> Create table student (rollno number (4) Primary key, name varchar2 (10));

SQL> Create table student (rollno number(4), name varchar2(10), primary key (rollno));

SQL> Create table student (rollno number(4) constraint rollno_pk primary key, name varchar2(10)); // Alternative way

Composite Primary key: Primary key can be defined for combination of columns also. When a primary key consists of multiple columns, then it is called a composite key.

Ex: assume a table Student having college,rollno and name columns, If we presume that students of different colleges can have the same rollno then we have to declare College+rollno as primary key.

SQL> create table student (rollno number (4), college varchar2(10), name varchar2 (10), Primary key (college,rollno));

4. Default: Default implies that if we do not specify the values in the insert statement, then the value specified in the default clause will be inserted defaultly.

SQL> Create table student(rollno number(4), marks number(4) default 0);

5. Check: Check constraint allows verifying the values being supplied against specified condition, e.g., age of employees between 20 and 50.

SQL> Create table emp(empid number(4), name varchar2(10), age number(2) check (age between 20 and 50));

SQL> Create table emp(empid number(4), name varchar2(10), age number(2) constraint age_ck check (age between 20 and 50)); // alternative way

6. Foreign Key: It designates a column as foreign key and establishes the relation between primary key column and foreign key column. It is also called reference ‘Reference Integrity Constraints’.

- The table which has foreign key is called child table and the table that contain primary key is called parent column.

SQL> Create table student(rollno number(4) primary key,name varchar2(10)); //parent table

SQL> Create table Result(rollno number(4) references student(rollno), subject varchar2(10), marks number(2)); // child table

SQL> Create table Result(rollno number(4), subject varchar2(10), marks number(2), foreign key(rollno) references student(rollno)); // alternative way of foreign key syntax

It determines how oracle maintains referential integrity, when primary key values are removed.

Options on delete while declaring foreign key:

On Delete Cascade: Is to specify that the dependency foreign key rows must be deleted, when primary key rows are removed.

SQL> Create table emp(empno number(4) primary key, empname varchar2(10), mgrno number(4) references emp(empno) on delete Cascade);

On Delete Set Null: It is to specify that the dependent foreign key values must be set to NULL when the primary key values are removed.

SQL> Create table emp(empno number(4) primary key, empname varchar2(10), mgrno number(4) references emp(empno) on delete set null);

Adding, Removing and altering constraints:

SQL> Alter table student add primary key (rollno);//adding primary key for existing table

SQL>Alter table student add constraint rollnopk primary key(rollno); // alternative way

SQL> Alter table student drop primary key // droping primary key constraint.

SQL> Alter table student drop constraint rollnopk; // alternative way

SQL> alter table student disable constraint rollnopk; // disabling

SQL> alter table student enable constraint rollnopk; // enabling