SQL SERVER -- Constraints -- Part-9

Constraint:-

                                         Constraints are created against table columns to validate the data which inserting

Types:-

1.   Unique

2.   Not Null

3.   Primary Key

4.   Check

5. Default

6.   Foreign Key

   UNIQUE:-

             It will not allow Duplicate records

             But it will allow one null value

-- UNIQUE

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT CONSTRAINT UQ_EMPID UNIQUE

 ,EMPNAME VARCHAR(50)

 ,SAL MONEY

 ,LOC VARCHAR(50)

 ,GENDER CHAR(6)

 ,DEPTNO INT

)

SELECT * FROM EMP

-- INSERT SAMPLE DATA

-- TESTING UNIQUE

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(2,'MANOJ',1000,'BANGALORE',104,'M')

INSERT INTO EMP(EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES('MANOJ',1000,'BANGALORE',104,'M')

SELECT * FROM EMP

   NOT NULL:-

            It will not allow any null values

-- 2. NOT NULL

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT

 ,EMPNAME VARCHAR(50) NOT NULL

 ,SAL MONEY

 ,LOC VARCHAR(50)

 ,GENDER CHAR(6)

 ,DEPTNO INT

)

-- HOW TO TEST

SELECT * FROM EMP

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(2,'MANOJ',1000,'BANGALORE',104,'M')

INSERT INTO EMP(EMPID ,SAL ,LOC,DEPTNO,GENDER)

VALUES(3,1000,'BANGALORE',104,'M')

PRIMARY KEY:-

             It will not allow Duplicate records

             And also will not allow null values

-- 3. PRIMARY KEY = UNIQUE + NOT NULL

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT CONSTRAINT PK_EMPID PRIMARY KEY

 ,EMPNAME VARCHAR(50)

 ,SAL MONEY

 ,LOC VARCHAR(50)

 ,GENDER CHAR(6)

 ,DEPTNO INT

)

-- TESTING PRIMARY KEY

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(2,'BALA',1000,'BANGALORE',104,'M')

INSERT INTO EMP(SAL ,LOC,DEPTNO,GENDER)

VALUES(2000,'BANGALORE',103,'M')

CHECK:-

               If we create check constraint on a column with a condition , while inserting the data it throws an error if the condition not satisfied.

-- 4. CHECK

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT

 ,EMPNAME VARCHAR(50)

 ,SAL MONEY CONSTRAINT CK_EMP_SAL CHECK (SAL>=15000)

 ,LOC VARCHAR(50)

 ,GENDER CHAR(6)

 ,DEPTNO INT

)

-- HOW TO TEST

SELECT * FROM EMP

-- SUCCESS

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(3,'MA',30000,'BANGALORE',104,'M')

-- ERROR

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(3,'MANOJ',12000,'BANGALORE',104,'M')

  DEFAULT:-

         Default Constraint is used to set some default value for any column. 

-- 5. DEFAULT

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT

 ,EMPNAME VARCHAR(50)

 ,SAL MONEY

 ,LOC VARCHAR(50) DEFAULT 'BANGALORE'

 ,GENDER CHAR(6)

 ,DEPTNO INT

)

-- HOW TO TEST

SELECT * FROM EMP

DROP TABLE EMP

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(3,'MANOJ',15001,'CHENNAI',104,'M')

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,DEPTNO,GENDER)

VALUES(3,'MANOJ',15001,'104','M')

     FOREIGN KEY:-

                        Foreign Key means while inserting the data into foreign key column, it will validate the data with referenced table column and insert into the table.

                  It fails to insert,if the value is not there in the referenced table column.

--5.FOREIGN KEY

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='EMP')

BEGIN

DROP TABLE EMP

END

CREATE TABLE EMP

(

  EMPID INT

 ,EMPNAME VARCHAR(50)

 ,SAL MONEY

 ,LOC VARCHAR(50)

 ,GENDER CHAR(6)

 ,DEPTNO INT CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY REFERENCES DEPT(DEPTID)

)

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(4,'KARTHIK',15001,'CHENNAI',101,'M')

INSERT INTO EMP(EMPID,EMPNAME ,SAL ,LOC,DEPTNO,GENDER)

VALUES(4,'KARTHIK',15001,'CHENNAI',106,'M')

-- Creaeting Dept Table

IF EXISTS(SELECT * FROM sys.tables WHERE NAME ='DEPT')

BEGIN

DROP TABLE DEPT

END

CREATE TABLE DEPT

(

  DEPTID INT PRIMARY KEY

 ,DEPTNAME VARCHAR(50)

)

-- HOW TO TEST

SELECT * FROM EMP

DROP TABLE DEPT

-- HOW TO SEE THE DATA

SELECT * FROM DEPT

-- INSERT SOME SAMPLE DATA INTO Dept Table.

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(101,'HR')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(102,'TESTING')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(103,'DEVOLOPMENT')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(104,'ADMIN')

INSERT INTO DEPT(DEPTID,DEPTNAME) VALUES(105,'FINANCE')