SQL CONSTRAINTS

CONSTRAINTS:

>Constraints are used to limit the type of data that can go into a table.
>constraints  are used apply business rules for the database tables

the constraints available in sql are
1. primary key  constraint
2. Foreign key   constraint
3. not null  constraint
4. unique key  constraint
5 .check constraint
6. default constraint


1.PRIMARY KEY:
                   it defines a column or combination of columns which uniquely identifies each row in the table


syntax: column_name datatype [constraint constraint_name] PRIMARY KEY

EX:




IF NOT EXISTS(SELECT * FROM SYS.OBJECTS WHERE name = 'BRANCH' AND [type] = 'U')
BEGIN
CREATE TABLE BRANCH
(
BRANCH_ID INT
CONSTRAINT PK_BRANCH_BRANCH_ID PRIMARY KEY ,
BRANCH_NAME VARCHAR(20)

)
END

Here primary key is applied to Branch_id column.

insert the records to the BRANCH table 

INSERT INTO BRANCH VALUES (1,'CSE')

INSERT INTO BRANCH VALUES (1,'IT') --ERROR Bcz Primary key violation duplicate values are not allowe

2.FOREIGN KEY:


This key refers some other columns and accepts only the values which available in it's parent key but allows
null values and duplicate values


EX:
TABLE1:

IF NOT EXISTS(SELECT * FROM SYS.OBJECTS WHERE name = 'BRANCH' AND [type] = 'U')

BEGIN

CREATE TABLE BRANCH

(

BRANCH_ID INT 

CONSTRAINT PK_BRANCH_BRANCH_ID PRIMARY KEY ,

BRANCH_NAME VARCHAR(20)

)

END

----insert the record 

INSERT INTO BRANCH VALUES (1,'CSE')

INSERT INTO BRANCH VALUES (2,'IT')

TABLE2:

CREATE TABLE FACULTY
       (
F_ID INT IDENTITY(100,1)
CONSTRAINT PK_FACULTY_F_ID PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
BRANCH_ID INT
        CONSTRAINT FK_FACULTY  FOREIGN KEY REFERENCES BRANCH(ID)
        )

--insert the records into faculty table

INSERT INTO FACULTY VALUES ('HARISH',1)  --INSERTED BCZ BRANCH_ID RECORD EXIST
                                                                                    IN BRANCH TABLE

INSERT INTO FACULTY VALUES ('ashok',40) ---error bcz foreign key violation id 40 is not exist in 
                                                                                   branch table





3.NOT NULL  CONSTRAINT:

                       doesn't allow the null values but allows the duplicates

ex: CREATE TABLE  MYTB
(
             ID INT(5),
             NAME VARCHAR(20)
                    CONSTRAINT NM_NN NOT NULL
)

insert records into mytb

INSERT INTO MYTB VALUES (1,'VENKAT') --INSERTED 
INSERT INTO MYTB(ID) VALUES (2)--error bcz not null 


4. UNIQUE KEY:
                   Doesn't allow the duplicates but allow the one   null value




ex:
 CREATE TABLE DEPT
(
DEPTNO TINYINT
                    CONSTRAINT PK_DEPTNO PRIMARY KEY,
DEPTNAME VARCHAR(10) NOT NULL,
DEPTLOCATION VARCHAR(10)
                        CONSTRAINT dp_loc_uk UNIQUE
)

insert the records

insert into dept (1,'cse','hyd')--inserted
insert into dept(2,'it',hyd)--error bcz unique key violation 




5.check constraint:    allows to create a domain using 'in' or 'between' and 'and' links to column to allow only those
set of values.



CREATE TABLE FACULTYINFO
(
EMP_ID SMALLINT CONSTRAINT PKEMPID PRIMARY KEY,
EMPNAME VARCHAR(20) NOT NULL,
EMP_ADDRESS VARCHAR(55)
              CONSTRAINT ADDDEFAULT DEFAULT 'HYD',
MOBILE VARCHAR(10),
EMAILID VARCHAR(10),
GENDER VARCHAR(1) CONSTRAINT CHKGENDER_ck CHECK (GENDER IN ('M','F')))

Here the check constraint is applied to Gender column values 'M','F' is allowed only



6.DEFAULT constraint:


                to set the default value for a column insted of taking as null.


    ex:CREATE TABLE FACULTYINFO
(
EMP_ID SMALLINT
                  CONSTRAINT PKEMPID PRIMARY KEY,
EMPNAME VARCHAR(20) NOT NULL,
EMP_ADDRESS VARCHAR(55)
                     CONSTRAINT ADDDEFAULT DEFAULT 'HYD',
MOBILE VARCHAR(10),
EMAILID VARCHAR(10),
GENDER VARCHAR(1)
                 CONSTRAINT CHKGENDER CHECK (GENDER IN ('M','F'))
)