SQL server Constraints explained

Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table.Constraints can be defined in two ways:

1. Column Level

   The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.

2. Table Level

   The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

1. Primary Key Constraints

   Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values.
   Primary key constraint at column level

   
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
   col2 datatype
   ); 
   
   

   Primary key constraint at table level

   
    ALTER TABLE table_name
   ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)
   
   

2. Unique Key Constraints

   Unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table.It is like Primary key but it can accept only one null value and it can not have duplicate values
   Unique key constraint at column level

   
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] UNIQUE,
   col2 datatype
   ); 
   
   

   Unique key constraint at table level

   
    ALTER TABLE table_name
   ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)
   
   

3. Foreign Key Constraints

   Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
   Foreign key constraint at column level

   
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
   col2 datatype
   ); 
   
   

   Foreign key constraint at table level

   
    ALTER TABLE table_name
   ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)
   
   

4. Not Null Constraints

   This constraint ensures that all rows in the database table must contain value for the column which is specified as not null means a null value is not allowed in that column.
   Not Null constraint at column level

   
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] NOT NULL, 
   col2 datatype
   );
   
   

   Not Null constraint at table level

   
    ALTER TABLE table_name
   ALTER COLUMN col1 datatype NOT NULL
   
   

5. Check Constraints

   This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.
   Check constraint at column level

   
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
   col2 datatype
   ); 
   
   

   Check constraint at table level

   
    ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition)