Column Level Constraints

Constraints can be added to table in two levels.

1. Column Level

2. Table Level

Column Level Constraints: Here constraints are going to placed on column the definition of each and every individual column and their corresponding type.

Syntax: (With out Constraint names)

CREATE TABLE TABLENAME
(COLUMN1 DATATYPE CONSTRAINTTYPE,COLUMN2 DATATYPE CONSTRAINTTYPE,......)

Example: CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR(20)UNIQUE,LOC VARCHAR(10)DEFAULT 'USA')

Example: CREATE TABLE EMP (EMPNO INT PRIMARY KEY,ENAME VARCHAR(20) NOT NULL,SAL MONEY CHECK(SAL>=1000),DEPTNO INT FOREIGN KEY REFERENCES DEPT (DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE)

Advantage of ON DELETE CASCADE:

With out specifying the ON DELETE CASCADE it is not possible to delete the record in the PARENT table if there are dependent records from the child table for that record. ON DELETE CASCADE if used when the record in the PARENT table is deleted all the dependent records in the child table will be also be deleted.

Advantages of ON UPDATE CASCADE:

With out specifying the ON UPDATE CASCADE it is not possible to update the record in the PARENT table if there are dependent records from the child table for that record. ON UPDATE CASCADE if used when the record in the PARENT table is updated all the dependent records in the child table will be also be updated.

Syntax:(With Constraint Names)

CREATE TABLE TABLENAME (COLUMN 1 DATA TYPE CONSTRAINT CONSTRIANTNAME CONSTRAINTTYPE,
COLUMN 1 DATA TYPE CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE,......)

Example:

CREATE TABLE DEPT (DEPTNO INT CONSTRAINT PK PRIMARY KEY,DNAME VARCHAR(20) CONSTRAINT UQ UNIQUE,LOC VARCHAR(10) CONSTRAINT DF DEFAULT 'HYD')

In the above example constraint have been placed on columns with constraint names like

Pk name of Primary Key Constraint on Deptno column

UK name of Unique Constraint on Dname column

DF name of Default Constraint of Loc column

Example: CREATE TABLE EMP (EMPNO INT CONSTRAINT PRK PRIMARYKEY,ENAME VARCHAR(20) CONSTRAINT NN NOT NULL, SAL MONEY CONSTRAINT CK CHECK(SAL>=1000),DEPTNO INT FOREIGN KEY CONSTRAINT FK REFERENCES DEPT (DEPTNO) ON DELETE CASECADE ON UPDATE CASCADE)

In the above example constraint have been placed on columns with constraint names like

PRK name of Primary Key Constraint on Empno column

NN name of Not Null Constraint on Ename column

CK name of Check Constraint on Sal column

Fk name of Foreign Key Constraint on Deptno column.

Column Level Constraints