18. INTEGRITY CONSTRAINTS

INTEGRITY CONSTRAINTS

DEFINITION: They are the rules or restrictions, which are imposed on a table column to restrict the invalid data.

CONSTRAINT KEYS:

·         PRIMARY KEY(UNIQUE+NOT NULL)

·         UNIQUE

·         NOT NULL

·         CHECK

·         FOREIGN KEY

·         REF

·         DEFAULT(KEY WORD)

PRIMARY KEY:

• ·         It acts has both UNIQUE+NOT null
• ·         There must be only one primary key for an entire table
• ·         Table which are having primary key constraint are called as Master tables (0r) parent tables

• ·         A primary key can contain n number of columns; such keys are called as composite keys
• ·         Implicitly an unique index get defined on a primary key column
• ·         A primary key can hold maximum of 32 columns

UNIQUE:

·         Won’t allow duplicate values, for even unique constraints also an unique index get defined.

·         It allows null values.

NOT NULL: Won’t allow null values, but allows duplicate values.

DEFAULT: To provide default values

FOREIGN KEY:

·         It is a referential constraint, which refers to the primary key or unique key.

·         It allows nulls, duplicates.

CHECK:

·         We use check constraints to fulfill the user requirements.

·         To enforce business rules.

  

  WE CAN PROVIDE CONSTRAINTS IN TWO WAYS

• ·         WHILE DEFING TABLES(CREATE)
• ·         ON EXISTING TABLE (ALTER)

·         WHILE DEFINING TABLES:

• ·         COLUMN LEVEL(INLINE)
• ·         TABLE LEVEL(OUT OF LINE)

  WE CAN PROVIDE NAMES TO CONSTRAINTS.

·         USER DEFINED

·         SYSTEM DEFINED

   By taking all the above things into consideration, we can provide the

constraints in following wayss

·         SYSTEM DEFINED COLUMN LEVEL

·         USER DEFINED COLUMN LEVEL

·         SYSTEM DEFINED TABLE LEVEL

·         USER DEFINED TABLE LEVEL

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

SYSTEM DEFINED COLUMN LEVEL:

CREATE TABLE CONTAB

(

     SNO                    NUMBER(5) PRIMARY KEY,

     SNAME                VARCHAR2(10) UNIQUE,

     LOC                     VARCHAR2(10) NOT NULL,

     BAL                     NUMBER(5) CHECK(BAL>1000),

     BANK                  VARCHAR2(10) DEFAULT ‘SBI’

);

CREATE TABLE CONTAB1 

(

   SNO           NUMBER (5) REFERENCES CONTAB (SNO),

   LOC            VARCHAR2 (10)

);

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

USER DEFINED COLUMN LEVEL:

CREATE TABLE CONTAB2 

(

    SNO                NUMBER (5) CONSTRAINT BY PRIMARY KEY,           

    SNAME            VARCHAR2(10) CONSTRAINT UN UNIQUE,  

    LOC                VARCHAR2(10) CONSTRAINT NN NOT NULL,   

    BAL                 NUMBER(5) CONSTRAINT CHK CHECK(BAL>1000)

);

CREATE TABLE CONTAB3

(

   SNO                NUMBER(5) CONSTRAINT FY REFERENCES CONTAB1(SNO),

   LOC                 VARCHAR2(10)

);

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

SYSTEM DEFINED TABLE LEVEL:

NOTE: Not possible to provide ‘NOT NULL’ constraints in table level.

  CREATE TABLE CONTAB4 

  (

   SNO           NUMBER(5),

   SNAME       VARCHAR2(10),

   LOC            VARCHAR2 (10),

   BAL            NUMBER(5),

  PRIMARY KEY(SNO),

  UNIQUE(SNAME),

  CHECK (BAL BETWEEN 1000 AND 2000)

  );

   CREATE TABLE CONTAB5

  (

    SNO            NUMBER(5),

    LOC            VARCHAR2(10),

    FOREIGN KEY(SNO) REFERENCES CONTAB4(SNO)

   );

USER DEFINED TABLE LEVEL:

   CREATE TABLE CONTAB6

   (

      SNO                  NUMBER(5),

      SNAME              VARCHAR2(10),

      LOC                  VARCHAR2(10),

      BAL                   NUMBER(5),

      CONSTRAINT PY1 PRIMARY KEY(SNO),

      CONSTRAINT UN1 UNIQUE(SNAME),

      CONSTRAINT CHK1 CHECK(SNAME IN (‘A’,’B’)

   );

   CREATE TABLE CONTAB7

   (

      SNO               NUMBER(5),

      LOC               VARCHAR2(10),

      CONSTRAINT FY1 FOREIGN KEY(SNO) REFERENCES CONTAB6(SNO)

    );

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

MIXED METHOD:

CREATE TABLE CONTAB8

(

   SNO                 NUMBER(5) PRIMARY KEY,

   SNAME             VARCHAR2(10),

   LOC                  VARCHAR2(10),

   BAL                  NUMBER(5),

   CHECK(BAL>2000)

 );

ADV OF TABLE LEVEL: Column comparison is possible unlike column level;

    EG: CREATE TABLE TL

            (

              X     NUMBER (5),

              Y     NUMBER(5),

              CHECK(X>Y)

             );

COMPOSITE PRIMARY KEY: When a primary key holds more than one column, those are  said to be composite primary keys.

EG: CREATE TABLE CONTABX

       (

         X           NUMBER(5),

         Y           VARCHAR2(10),

         LOC       VARCHAR2(10),

         CONSTRAINT CPY PRIMARY KEY(X,Y)

        );

CREATE TABLE CONTABY

(

  S           NUMBER(5),

  Y           VARCHAR2(10),

  CONSTRAINT CFY FOREIGN KEY(X, Y) REFERENCES CONTABX(X,Y)

 );

NOTE:  When you refer a foreign key to composite primary key,

                Foreign key also has to contain that many no of columns.

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

SELF KEY: Referring a foreign key with in a same table primary key column.

EG: CREATE TABLE SELFTAB 

       (

         SNO              NUMBER (5) PRIMARY KEY, 

         LOC               VARCHAR2 (10), 

         ID                 NUMBER (5) REFERENCES SELFTAB (SNO)

        );