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

SQL Integrity Constraints or Constraints

  Author : Shailendra Chauhan
Posted On : 02 Feb 2011
Total Views : 131,137   
Updated On : 25 Sep 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) 



Free Interview Books
 
COMMENTS (0)
3 AUG
ASP.NET Core with Angular4 Development (Online)

07:00 AM - 9:00 AM IST(+5:30 GMT)

Know More
29 JUL
Xamarin Forms : Build Cross-platform Apps (Classroom)

09:00 AM - 11:00 AM IST(+5:30 GMT)

Know More
24 JUL
ASP.NET MVC with Angular4 Development (Online)

07:00 AM - 09:00 AM IST(+5.30 GMT)

Know More
22 JUL
Angular2 and Angular4 Development (Online)

08:00 AM - 10:00 AM IST(+5.30 GMT)

Know More
22 JUL
ASP.NET MVC with Angular4 Development (Classroom)

09:30 AM - 11:00 AM IST (+5:30GMT)

Know More
22 JUL
MEAN Stack 2 Development (Classroom)

04:00 PM - 05:30 PM IST(+5:30GMT)

Know More
22 JUL
Angular2 and Angular4 Development (Online)

08:00 AM - 10:00 AM IST(+5.30 GMT)

22 JUL
ASP.NET MVC with Angular4 Development (Classroom)

09:30 AM - 11:00 AM IST (+5:30GMT)

22 JUL
MEAN Stack 2 Development (Classroom)

04:00 PM - 05:30 PM IST(+5:30GMT)

20 JUL
Hadoop Development cum Administration (Online)

07:00 AM - 09:00 AM IST(+5:30 GMT)

17 JUL
NodeJS with Angular4 Development (Online)

08:00 PM - 09:30 PM IST(+5.30 GMT)

15 JUL
ASP.NET MVC with Angular4 Development (Online)

03:30 PM - 05:30 PM IST(+5.30 GMT)

1 JUL
ASP.NET MVC with Angular4 Development (Classroom)

08:00 AM - 09:30 AM IST

1 JUL
MEAN Stack 2 Development (Classroom)

11:00 AM - 12:30 PM IST(+5:30 GMT)

30 JUN
Xamarin Forms : Build Cross-platform Apps (Online)

09:30 PM - 11:00PM IST(+5:30GMT)

6 JUN
ASP.NET MVC with Angular4 Development (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

20 MAY
NodeJS with Angular4 Development (Classroom)

05:00 PM - 07:00 PM

2 MAY
ASP.NET Core Development (Online)

07:00 AM - 09:00 AM IST(+5.30 GMT)

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+