SQL Constraints

Constraints are categorized as follows.

Domain integrity Constraints	Entity integrity Constraints	Referential integrity Constraints
Not null	Unique	Foreign key
Check	Primary key

 

Constraints are always attached to a column not a table.

We can add constraints in three ways.

Column level -- Along with the column definition

Table level     -- After the table definition

Alter level      -- Using alter command

While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.

If you want to give a name to the constraint, you have to use the constraint clause.

NOT NULL

This is used to avoid null values.

We can add this constraint in column level only.

Example:

     

SQL>CREATE TABLE Student

(

   no      NUMBER (2) NOT NULL,

   name    VARCHAR (10),

   marks   NUMBER (3)

);

SQL> CREATE TABLE Student1

(

   no      NUMBER (2) CONSTRAINT nn NOT NULL,

   name    VARCHAR (10),

   marks   NUMBER (3)

);

CHECK

This is used to insert the values based on specified condition.

We can add this constraint in all three levels.

Example:

    COLUMN LEVEL

     SQL> CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3) CHECK (marks > 300)

);

      SQL> CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3) CONSTRAINT ch CHECK (marks > 300)

);

      TABLE LEVEL

      SQL> CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CHECK (marks > 300)

);     

    SQL> CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT ch CHECK (marks > 300)

);

     ALTER LEVEL
 

SQL> ALTER TABLE student ADD CHECK(marks>300);

SQL> ALTER TABLE student ADD CONSTRAINT ch CHECK(marks>300);

UNIQUE

This is used to avoid duplicates but it allow nulls.

We can add this constraint in all three levels.

Example:

COLUMN LEVEL

 SQL>CREATE TABLE student

(

   no      NUMBER (2) UNIQUE,

   name    VARCHAR (10),

   marks   NUMBER (3)

);

  SQL>CREATE TABLE student

(

   no      NUMBER (2) CONSTRAINT un UNIQUE,

   name    VARCHAR (10),

   marks   NUMBER (3)

);

 

TABLE LEVEL

 SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   UNIQUE (no)

);

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT un UNIQUE (no)

);

 ALTER LEVEL

SQL>ALTER TABLE student ADD UNIQUE(no);

                       SQL> ALTER TABLE student ADD CONSTRAINT

PRIMARY KEY

This is used to avoid duplicates and nulls. This will work as combination of unique and not null.

Primary key always attached to the parent table.

We can add this constraint in all three levels.

Example:

      COLUMN LEVEL

     

SQL>CREATE TABLE student

(

   no      NUMBER (2) PRIMARY KEY,

   name    VARCHAR (10),

   marks   NUMBER (3)

);

SQL>CREATE TABLE student

(

   no      NUMBER (2) CONSTRAINT pk PRIMARY KEY,

   name    VARCHAR (10),

   marks   NUMBER (3)

); un UNIQUE(no);

    

      TABLE LEVEL

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   PRIMARY KEY (no)

);

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT pk PRIMARY KEY (no)

);

    

      ALTER LEVEL

    

SQL> ALTER TABLE student ADD PRIMARY KEY(no);

 SQL> ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(no);

FOREIGN KEY

This is used to reference the parent table primary key column which allows duplicates.

Foreign key always attached to the child table.

We can add this constraint in table and alter levels only.

Example:

      TABLE LEVEL    

 SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   PRIMARY KEY (empno),

   FOREIGN KEY (deptno) REFERENCES dept (deptno)

);

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   CONSTRAINT pk PRIMARY KEY (empno),

   CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES dept (deptno)

);

      ALTER LEVEL

   

SQL> ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);

SQL> ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno);

Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.

 

USING ON DELETE CASCADE

      
By using this clause you can remove the parent record even it childs exists.

Because when ever you remove parent record oracle automatically removes all its dependent records from child table, if this clause is present while creating foreign key constraint.

Ex:

      TABLE LEVEL

  

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   PRIMARY KEY (empno),

   FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE

);

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   CONSTRAINT pk PRIMARY KEY (empno),

   CONSTRAINT fk FOREIGN KEY

      (deptno)

       REFERENCES dept (deptno) ON DELETE CASCADE

);

    

      ALTER LEVEL

     

SQL> ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

SQL> ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES ept(deptno) ON DELETE CASCADE;

 

COMPOSITE KEYS

A composite key can be defined on a combination of columns.

We can define composite keys on entity integrity and referential integrity constraints.

Composite key can be defined in table and alter levels only.

Example:

      UNIQUE (TABLE LEVEL)

    

     

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   UNIQUE (no, name)

);

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT un UNIQUE (no, name)

);

      UNIQUE (ALTER LEVEL)

     

SQL> ALTER TABLE student ADD UNIQUE(no,name);

SQL> ALTER TABLE student ADD CONSTRAINT un UNIQUE(no,name);

     PRIMARY KEY (TABLE LEVEL)

     

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   PRIMARY KEY (no, name)

);

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT pk PRIMARY KEY (no, name)

);

      PRIMARY KEY (ALTER LEVEL)

     

SQL> ALTER TABLE student ADD PRIMARY KEY(no,anme);

SQL> ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(no,name);

      FOREIGN KEY (TABLE LEVEL)

   

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   dname    VARCHAR (10),

   PRIMARY KEY (empno),

   FOREIGN KEY (deptno, dname) REFERENCES dept (deptno, dname)

);

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   dname    VARCHAR (10),

   CONSTRAINT pk PRIMARY KEY (empno),

   CONSTRAINT fk FOREIGN KEY (deptno, dname) REFERENCES dept (deptno, dname)

);

      FOREIGN KEY (ALTER LEVEL)   

   

SQL>  ALTER TABLE emp ADD FOREIGN KEY(deptno,dname) REFERENCES dept(deptno,dname);

SQL> ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno,dname) REFERENCES dept(deptno,dname);

DEFERRABLE CONSTRAINTS

Each constraint has two additional attributes to support deferred checking of constraints.

Deferred initially immediate

Deferred initially deferred

Deferred initially immediate checks for constraint violation at the time of insert.

Deferred initially deferred checks for constraint violation at the time of commit.

Example:

  

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT un UNIQUE (no) DEFERRED initially immediate);

SQL>CREATE TABLE student

(

   no      NUMBER (2),

   name    VARCHAR (10),

   marks   NUMBER (3),

   CONSTRAINT un UNIQUE (no) DEFERRED initially deferred);

SQL > ALTER TABLE student ADD CONSTRAINT un UNIQUE(no)   DEFERRABLE INITIALLY DEFERRED;

    

   

SQL> SET CONSTRAINTS ALL IMMEDIATE;

     This will enable all the constraints violations at the time of inserting.

    

SQL> SET CONSTRAINTS ALL DEFERRED;

     This will enable all the constraints violations at the time of commit.

DEFAULT

Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.

When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.

Example: 

SQL>CREATE TABLE student

          (

            no     NUMBER (2) DEFAULT 11,

            name   VARCHAR (2)

         );

          SQL>INSERT INTO student

                       VALUES (1, 'a');

         SQL>INSERT INTO student (name)

                     VALUES ('b');

   

         SQL> SELECT * FROM student;

        NO   NAME

      ------ ---------

         1             a

        11            b

       SQL>INSERT INTO student

                                           VALUES (NULL, ‘c’);

      SQL> SELECT * FROM student;

        NO   NAME

      ------ ---------

         1             a

        11            b

                     C

-- Default can not override nulls.

OPERATIONS WITH CONSTRAINTS

Possible operations with constraints as follows.

Enable

Disable

Enforce

Drop

ENABLE CONSTRAINTS

 

This will enable the constraint. Before enable, the constraint will check the existing data.

Example:

     SQL>ALTER TABLE student ENABLE CONSTRAINT un;

DISABLE CONSTRAINTS

This will disable the constraint.

Example:

     SQL>ALTER TABLE student DISABLE CONSTRAINT un;

ENFORCE CONSTRAINTS

This will enforce the constraint rather than enable for future inserts or updates.

This will not check for existing data while enforcing data.

Example:

     SQL>ALTER TABLE   student  ENFORCE CONSTRAINT un;

DROP CONSTRAINTS

This will remove the constraint.

Example:

     SQL>ALTER TABLE student DROP CONSTRAINT un;

Once the table is dropped, constraints automatically will drop.