Integrity Constraints

Introduction – Integrity Constraints or Constraints are the rules to permit or restrict what values can be stored in columns. it is used to make sure consistency and accuracy of data in a relational database tables. It is used to limit the type of data that can be inserting into a table. In simple language, Integrity Constraints are used to apply business rules for the tables.

Common types of SQL Constraints – 

S.No.ConstraintsDescription
01NOT NULLIt is used to make sure that a Column can not have any null value.
02DEFAULTIt is used to Provides default value for a column.
03UNIQUEIt is used to make sure that all value of a column are unique or different.
04CHECKIt is used to gives a criteria for all values of a column.
05PRIMARY KEYIt is used to uniquely identify a record in the table.
06FOREIGN KEYIt make sure to referential Integrity of the relational data.

1. NOT NULL Constraint – 

(Create table with NOT NULL Constraint)

 CREATE TABLE Students

 (

    S_ID integer NOT NULL PRIMARY KEY,

    NAME char(30) NOT NULL,

    CLASS char(4),

    GENDER enum(‘M’, ‘F’, ‘O’),

 );

2. DEFAULT Constraint – 

(Create table with DEFAULT Constraint)

 CREATE TABLE Students

 (

    STUDENT_ID integer NOT NULL PRIMARY KEY,

    S_NAME char(30) NOT NULL,

    CLASS char(4) DEFAULT ‘XII’,,

    GENDER enum(‘M’, ‘F’, ‘O’),

     BIRTH_DATE date,

     AGE integer DEFAULT 17,

    School_Name varchar(40) DEFAULT ‘KV HARDA’,

 );

3. CHECK Constraint – 

(Create table with CHECK Constraint)

 CREATE TABLE Students

 (

    S_ID integer PRIMARY KEY CHECK(S_ID>1000),

    NAME char(30) NOT NULL,

    GENDER enum(‘M’, ‘F’, ‘O’),

    STREAM enum(‘MATH’, ‘COMMERCE’, ‘BIOLOGY’ ,’ART’),

    CLASS char(4),

    SECTION enum(‘A’, ‘B’, ‘C’, ‘Z’),

    DOB date,

    School_Name varchar(40) DEFAULT ‘KV HARDA’,

    CHECK (DOB > ‘1995-01-01’)

 );

4. UNIQUE KEY Constraint – 

(Create table with unique key Constraint)

CREATE TABLE Students

 (

    S_ID integer PRIMARY KEY,

    NAME char(30) NOT NULL,

    GENDER enum(‘M’, ‘F’, ‘O’),

    SUBJECT enum(‘MATH’, ‘COMMERCE’, ‘BIOLOGY’ ,’ART’),

    CLASS char(4),

    SECTION enum(‘A’, ‘B’, ‘C’, ‘Z’),

    MOBILE_NO char(10) UNIQUE,

    AADHAR_NO char(12),

    School_Name varchar(40)DEFAULT ‘KV HARDA’,

    CHECK (ID>1000),

    UNIQUE (AADHAR_NO)

 );

5. PRIMARY KEY Constraint – 

(Create table with Primary key Constraint as column constraint)

 CREATE TABLE Teachers

 (

    T_ID integer PRIMARY KEY,

    T_NAME char(30) NOT NULL,

    CLASS_TEACHER char(4),

    School_NAME varchar(40) DEFAULT ‘KVS’,

    CHECK (T_ID>1000)

 );

(Create table with Primary key Constraint as table constraint)

 CREATE TABLE Teachers

 (

    T_ID integer,

    T_NAME char(30) NOT NULL,

    CLASS_TEACHER char(4),

    School_NAME varchar(40) DEFAULT ‘KVS’,

    CHECK (ID>1000)

    PRIMARY KEY (T_ID)

 );

6. FOREIGN KEY Constraint – 

(Create table with Foreign Key Constraint) 

Before you implement a foreign key constraints, carefully go through the definition of foreign key that states that a Foreign key is a non-key column of a table(called child table) that draw its values from primary key(or unique key) of another table (called parent table).

 Command (without naming the foreign key constraint):

   CREATE TABLE  child-table-name

   ( 

      column-name  data-type (size),

      column-name  data-type (size),

      column-name  data-type (size),

FOREIGN KEY(column-name) REFERENCES parent-table-name(primary-key-column-name);

   );

Example: (assume that parent table name is students and its primary key column name is S_ID )

   CREATE TABLE  Student_Tbl

   ( 

      ID  integer,

      address  char(40),

      Aadhar_No  char(12),

     FOREIGN KEY(ID) REFERENCES Students(ID)

   );

Naming the Foreign Key Constraint with multiple foreign key  

   CREATE TABLE  Student_Tbl

   ( 

      ID  integer,

      address  char(40),

      Aadhar_No  char(12),

CONSTRAINT f_key1 FOREIGN KEY(ID) REFERENCES Students(S_ID),

CONSTRAINT f_key2 FOREIGN KEY(Aadhar_NoREFERENCES Students(aadhar)

   );

Referencing action that decides what action to take place in case of DELETE or UPDATE operation occurs in the parent table, means if primary key of parent table will be delete or update then what will be do with foreign key of child table.

S.No.ActionChange in Parent TableChange in Child Table
01CASCADEON UPDATE
ON DELETE
UPDATE ROW
DELETE ROW
02SET NULLON UPDATE
ON DELETE
NULL VALUE SET
03NO ACTIONON UPDATE
ON DELETE
NO CHANGE IN CHILD TABLE
04RESTRICT ON UPDATE
ON DELETE
(action rejects)
NO CHANGE IN CHILD TABLE

 you can specify the referencing action through following clauses:

ON DELETE Clause 

ON UPDATE Clause

 Examples:

   CREATE TABLE  Student_Tbl

   ( 

      ID  integer,

      address  char(40),

      Aadhar_No  char(12),

CONSTRAINT f_key1 FOREIGN KEY(IDREFERENCES Students(S_ID)

ON UPDATE CASCADE 

ON DELETE SET NULL,

CONSTRAINT f_key2 FOREIGN KEY(Aadhar_NoREFERENCES Students(aadhar

ON UPDATE RESTRICT 

ON DELETE NO ACTION

   );

Leave a Reply

Your email address will not be published. Required fields are marked *