Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Integrity Constraints or Constraints

  Author : Shailendra Chauhan
Updated On : 25 Sep 2012
Total Views : 125,123   
Support : SQL Server 2005,2008,2012
 

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) 

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (09:30 AM-11:00 AM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+