SQL Tutorial : DML - ADDING CONSTRAINTS

SQL Tutorial : DML - ADDING CONSTRAINTS

Let's learn about next topic: CONSTRAINTS

CONSTRAINTS:

***************

PRIMARY KEY => UNIQUE +NOT NULL

NOT NULL => NO NULL VALUES ALLOWED

UNIQUE=> UNIQUE + NULL

CHECK => CONDITIONS ( SALARY>5000)

FOREIGN KEY  => CAN HAVE REPEATED AND NULL VALUES .

VALUES CAN BE SAME AS THE REFERENCED 
PRIMARY KEY COLUMN OF THE PARENT TABLE

- DURING CREATION OF TABLE

1.AT COLUMN LEVEL

2.TABLE LEVEL

- AFTER CREATION OF TABLE

AT COLUMN LEVEL:

******************

DD: USER_CONSTRAINTS

      USER_CONS_COLUMNS

SYS_CN ( N => NUMBER)

NOT NULL CONSTRAINTS CAN BE GIVEN ONLY AT

COLUMN LEVEL

- CREATE TABLE COURSE

( CID NUMBER(3) CONSTRAINT CRS_CID_PK  PRIMARY KEY ,

 CNAME VARCHAR2(20) NOT NULL )

- CREATE TABLE STUDENTS

(SID NUMBER(3) CONSTRAINT   STUDS_SID_PK PRIMARY KEY ,

 SNAME VARCHAR2(20) CONSTRAINT  STUD_SNME_NN NOT NULL,

 SEML    VARCHAR2(20) CONSTRAINT  STUD_SEML_UNIQUE UNIQUE,

 SFEES   NUMBER(5) CONSTRAINT  STUD_SFEES_CHECK

 CHECK (SFEES> 10000) ,

 SCID   NUMBER(4)

 CONSTRAINT STUD_SCID_FK REFERENCES COURSE(CID))

- CREATE TABLE STUDENTS

(SID NUMBER(3),

 SNAME VARCHAR2(20) CONSTRAINT  STUD_SNME_NN NOT NULL,

 SEML    VARCHAR2(20) ,

 SFEES   NUMBER(5),

 SCID   NUMBER(4) ,

CONSTRAINT   STUDS_SID_PK PRIMARY KEY (SID),

CONSTRAINT  STUD_SEML_UNIQUE UNIQUE(SEML),

CONSTRAINT  STUD_SFEES_CHECK

    CHECK (SFEES> 10000) ,

CONSTRAINT STUD_SCID_FK FOREIGN KEY (SCID)

                            REFERENCES COURSE(CID)

[ON DELETE CASCADE |ON DELETE SET NULL])

- TABLE LEVEL CONSTRAINTS FOR

COMPOSITE KEYS AND MULTIPLE COLUMN CHECK CONSTRAINTS

100   A    

101   B 20       20 SERVICE

- ALTER TABLE STUDENTS

ADD CONSTRAINT  STUDS_SID_PK PRIMARY KEY (SID)

- ALTER TABLE STUDENTS

ADD CONSTRAINT  STUDS_SEML_UNQ UNIQUE(SEML)

- ALTER TABLE STUDENTS

ADD CONSTRAINT  STUDS_SFEES_CHL  CHECK(SFEES>10000)

- ALTER TABLE STUDENTS

MODIFY SNAME VARCHAR2(20) CONSTRAINT SNAME_NN NOT NULL

- ALTER TABLE STUDENTS

DISABLE CONSTRAINT STUDS_SID_PK;

- ALTER TABLE STUDENTS

DISABLE CONSTRAINT STUDS_SID_PK CASCADE ;

- ALTER TABLE STUDENTS

ENABLE CONSTRAINT STUDS_SID_PK;

- ALTER TABLE STUDENTS

DROP  CONSTRAINT STUDS_SID_PK;

- ALTER TABLE STUDENTS

DROP  CONSTRAINT STUDS_SID_PK CASCADE