19. CASCADE CONSTRAINT

CASCADE CONSTRAINT:

o   Dropping master table directly is not possible when it is having child tables. We have to drop child tables before dropping master tables, but by using ‘cascade constraint’.

o   It is possible.

EG: DROP TABLE CONTAB; (ERROR);

      DROP TABLE CONTAB CASCADE CONSTRAINT;

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

• ON DELETE CASCADE
• ON DELETE SET NULL
• ON DELETE RESTICT (DEFAULT)

ON DELETE CASCADE:

• It is not possible to delete the parent records when they are having dependent child records very directly, but by using on delete cascade it is possible.
• We have to mention on delete cascade while defining foreign key.
•  It is not possible to provide on existing foreign key.

EG: CREATE TABLE SAM

       ( 

         SNO       NUMBER(5) PRIMARY KEY 

        );

       INSERT INTO SAM VALUES(10);

       INSERT INTO SAM VALUES(20);

       INSERT INTO SAM VALUES(30);

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

       CREATE TABLE SAMC

        (

          SNO              NUMBER(5) REFERENCES SAM(SNO) ON DELETE                                                                                                            CASCADE

         );

       INSERT INTO SAMC VALUES(10);

       INSERT INTO SAMC VALUES(20);

       DELETE FROM SAM WHERE SNO=10;

       (SAMC TABLE 10 VALUES ALSO GET DELETED);

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

ON DELETE SET NULL:

·         Instead of deleting dependent records ,on delete set null  sets the null value.

·         DEFINING CONSTRAINTS ON EXISTING TABLE:

GENERIC SYNTAX:

ALTER TABLE TABLENAME ADD|MODIFY|DISABLE|ENABLE(VALIDATE|INVALIDATE)|ENFORCE|DROP

   |RENAME  CONSTRAINT CONSTRAINTNAME;

 EG:

      CREATE TABLE CON21

       (

         SNO           NUMBER(5),

         SNAME       VARCHAR2(10),

         BAL            NUMBER(5),

         LOC            VARCHAR2(10)

       );

  

ADD:

   ALTER TABLE CON21 ADD CONSTRAINT KP10 PRIMARY KEY(SNO);

   ALTER TABLE CON21 ADD CONSTRAINT CHK10 CHECK(BAL>100)

ADD  UNIQUE(LOC);

NOTE: NOT POSSIBLE TO ADD ‘NOT NULL ‘CONSTRAINT;

MODIFY: We will modify not null to null and vice versa.

 SYN: ALTER TABLE TABLENAME MODIFY COLUMNNAME NOT NULL|NULL;

 EG: ALTER TABLE CON21 MODIFY SNAME NOT NULL;

       INSERT INTO CON21 VALUES (10, NULL, 8000,’HYD’); ERROR;

       ALTER TABLE CON21 MODIFY SNAME NULL;

       INSERT INTO CON21 VALUES (10, NULL, 8000,’HYD’); ROW CREATED;

       ALTER TABLE CON21 MODIFY SNAME NOT NULL; (ERROR, NULL FOUND);

DISABLE:

EG:   ALTER TABLE CON21 DISABLE CONSTRAINT KP10;

        INSERT INTO CON21 VALUES (10, NULL, 4000,’BAN’);

        INSERT INTO CON21 VALUES (10, NULL, 4000,’C’); DUPLICATE VALUES;

        SELECT * FROM CON21;

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

ENABLE:

NOTE: BEFORE ENABLING A CONSTRAINT OR IMPOSING CONSTRAINT ON A COLUMN, COLUM DATA MUST NOT VOILATE THE RULES;

EG: ALTER TABLE CON21 ENABLE CONSTRAINT KP10;

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

DROP:

EG: ALTER TABLE CON21 DROP CONSTRAINT KP10;

NOTE: WHEN YOU DROP TABLE AUTOMATICAL CONSTRAINTS GET DROPED;

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

RENAME:

SYN: ALTER TABLE TABLENAME RENAME CONSTRAINT CONSTRAINTNAME TO

  NEWNAME;

Note: CHECK CONSTRAINT WON’T ALLOW FOLLOWING THINGS.

·         SUBQUERY

·         SCALAR SUBQUERY

·         NEXTVAL

·         CURRVAL

·         LEVEL

·         ROWNUM

·         SYSDATE

·         USER

·         UID

·         USERENV

·         DBTIMEZONE

·         SESSION TIMEZONE

·         TIMESTAMP WITH TIMEZONE