SQL Server: How to define constraints in Table Creation? Constraints in SQL Server

What is a constraint?

A constraint is a validation property assigned to a column or the set of fields or columns in a table that prevents invalid inputs to that field or column. They are used to maintain the database integrity in DBMS.  

Not Null	By this constraint, a column cannot have a NULL value
Unique	By this constraint all values in a column are different.
Primary Key	By this constraint, every record can be uniquely identified in a table
Foreign Key	By this constraint, we can relate a row/record in another table.
Default	By this constraint, we can set a default value for a column when no value is specified. For example, if you have not supplied Date value to a date field, the default date will be placed on date column 1900-01-01.
Check	By this constraint, we can ensure that all values in a column satisfies a specific condition.
Index	By this constraint, we can create and retrieve data from the database very quickly.

Example

--A table with not null, unique, primary key, 

--check and default constraints implemented

Create table tabEmployee(

EmpID int primary key,

EmpName varchar(20) not null,

Age int not null check (Age>=18),

Email varchar(20) unique,

Designation varchar(20) not null,

Salary numeric default 8000

);

Try this following query, the record will be successfully get stored

insert into tabEmployee values(1000,'Asrafi',30,'asrafixyz@gmail.com','Consultant',95000)

(1 row affected)

Try this following query with the same EmpID, the record will not get stored, 

it will throw the error.

insert into tabEmployee values(1000,'Parvin',30,'asrafixyz@gmail.com','Consultant',95000)

Violation of PRIMARY KEY constraint 'PK__tabEmplo__AF2DBA7937BDFA7A'. Cannot insert duplicate key in object 'dbo.tabEmployee'. The duplicate key value is (1000).

The statement has been terminated.

Now try the same query with EmpNo as 1001, the record will be inserted.