Constraints and types of constraints in sql server

What is Constraint in SQL - Server ?
Ans:->  Constraint are using to restricted the insertion of unwanted data in any columns. we can create constraints on single or multiple columns of any table. It maintain the data integrity of  the table.

There are 6 types of constraints in Sql Server:-

    Primary key constraint.
    Foreign Key constraint.
    Unique Key constraint.
    Not Null constraint.
    Check constraint
    Default constraint

Description of  above constraints:-

1. Primary Key :- Primary Key of a relational table uniquely identifies each record in the table. It can be either be a normal attribute that is guaranteed to be unique such as in a school name should be same of any student but roll number never be same of any student in a school.

Creating primary key at the time of table creation.

CREATE TABLE EMP
(
EMPNUMBER INT PRIMARY KEY,
ENAME VARCHAR(30),
DOB DATETIME,
HIREDATE DATETIME,
SAL NUMERIC(13,2),
MGR INT,
DEPTNO INT
)

CREATE TABLE DEPT
(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(30),
)

Adding primary key after creating of table.

when we adding primary key in any table before adding primary key we have to create that column is not null if the column is not null then we have to create not null constraint on the column after that we will create primary key constraint on that table.

Syntax:-

ALTER TABLE ALTER COLUMN NOT NULL

ALTER TABLE  ADD CONSTRAINT   PRIMARY KEY (COLUMN_NAME)

Eg:-

ALTER TABLE EMP ALTER COLUMN EMPNUMBER INT NOT NULL
ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY(EMPNUMBER)

2. Foreign Key:- One of the most important concept in database is creating relationships between  database tables. these relationships provide a mechanism for linking data stored in multiple tables and retriving it in an efficient manner. in order to create a link between two tables we must specify a foreign key in one table that references a column in another table.

foreign key creating at the time of table creation:-
CREATE TABLE EMP
(
EMPNUMBER INT PRIMARY KEY,
ENAME VARCHAR(30),
DOB DATETIME,
HIREDATE DATETIME,
SAL NUMERIC(13,2),
MGR INT,
DEPTNO INT FOREIGN KEY REFERENCES DEPT(DEPTNO)
)

Another way to creating foreign key after creation of table

Syntax:-     
ALTER TABLE ADD CONSTRAINT FOREIGN KEY (COLUMN_NAME)REFERENCES ()
ALTER TABLE EMP ADD CONSTRAINT FK_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

3. Unique Key:-  Unique key constraint is use to make sure that there is no duplicate value in that column. Both unique key and primary key both enforces the uniqueness of column but there is one difference between them unique key constraint allow one null value but primary key does not null value.In a table we create one primary key but we can create more than one unique key in Sql Server.

Syntax to add unique key:-

ALTER TABLE ADD CONSTRAINT UNIQUE ()

EXAMPLE:-
ALTER TABLE EMP ADD CONSTRAINT UK_EMP UNIQUE(EMAIL,MOBILENO)

4. Not null constraint:- Not null constraint is used to restricted the insertion of null value at that column. Not null constraint is using for that column which is not ignorable.

Syntax to adding Not Null Constraint:-
ALTER TABLE ALTER COLUMN (DATA TYPE) NOT NULL

EXAMPLE:-

ALTER TABLE EMP ALTER COLUMN EMAIL VARCHAR(50) NOT NULL

5. Check Constraint :-  This constraint is using to check value at the time of insertion like as salary of any employee is always greater than zero. so we can create a check constraint on employee table which is greater than zero.

Syntax:-
ALTER TABLE ADD CONSTRAINT CHECK (COLUMN_NAME WITH CONDITION)

EXAMPLE:-
ALTER TABLE EMP ADD CONSTRAINT CK_GREATER_THAN_ZERO CHECK (SAL>0)

6. Default Constraint:- The Default constraint is using to set a specific value of column if we not passing the value at the time of insertion.Through this constraint we set the default value of column.

Syntax:-
ALTER TABLE ADD CONSTRAINT FOR (COLUMN NAME)
Eg:-
ALTER TABLE EMP ADD CONSTRAINT DF_SAL DEFAULT 5000 FOR SAL

Use of above all constraint at the time of creating table:-
CREATE TABLE EMP123(
EMPNUMBER INT PRIMARY KEY,
ENAME VARCHAR(30) DEFAULT 'NOT KNOWN',
DOB DATETIME NOT NULL,
HIREDATE DATETIME,
EMAIL VARCHAR(50) UNIQUE ,
MOBILE VARCHAR(10) UNIQUE,
SAL NUMERIC(13,2) CHECK (SAL>0)
,MGR INT FOREIGN KEY REFERENCES EMP(EMPNUMBER),
DEPTNO INT FOREIGN KEY REFERENCES DEPT(DEPTNO)
)

For removing the constraint :-
ALTER TABLE DROP CONSTRAINT
ALTER TABLE EMP DROP CONSTRAINT DF_SAL DEFAULT