SQL Server Constraints with Example


This article describes about SQL server constraits like Primary key, not null, Unique, Check, Default and foreign key with examples. It also gives syntax to add or drop constraints from table.

Constraints in Sql Server are some predefined set of rules that must be followed to maintain the correctness of the data. A constraint can be created while creating a table, or it can be added later on with Alter table command. If the constraint is added after the creation of table it first checks the existing data.

The constraints which can be applied on the table are.

  • Primary Key Constraint
  • Not Null Constraint
  • Unique Constraint
  • Check Constraint
  • Default Constraint
  • Foreign Key Constraint

  1. Primary key Constraint:

    A Primary key constraint is applied for uniquely identifying rows in a table. It cannot contain Null values and rest of table data should be unique. While creating a table if we do not specify a name to the constraint, sql server automatically assigns a name to the constraint.

    Below is example to create a Primary Key Constraint. Column EmpID of table EmployeeDetails is specified as Primary Key. Hence EmpID cannot have duplicate and null values.

        Create table EmployeeDetails
        (
        EmpID int PRIMARY Key ,
        EmpFirstName varchar(80),
        EmpLastName varchar(80),
        Department varchar(30),
        DepartID int
        )
    
                    

    To view the constraints on the table use the below given query, here we have not given the name to the constraint so a name has been specified by SQL server.

        SELECT CONSTRAINT_NAME,
         TABLE_SCHEMA ,
         TABLE_NAME,
         CONSTRAINT_TYPE
         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME='EmployeeDetails'
                    

    sql server primary key

    If we want to provide a user defined name to primarykey, below given query can be used.

        Create table EmployeeDetails 
        (
        EmpID int Constraint empids_PK PRIMARY Key ,
        EmpFirstName varchar(80),
        EmpLastName varchar(80),
        Department varchar(30),
        DepartID int
        )
    
                    

    Primary key is called as Composite Primary key when applied to more than one column.

  2. Not Null Constraint

    A Not Null constraint ensures that the column in the table cannot have Null values, however it may have duplicate values. The below given query shows the working of Not Null constraint applied on DepartID column of EmployeeDetails table.

    CREATE TABLE EmployeeDetails 
    (
    EmpID INT,
    EmpFirstName VARCHAR(80),
    EmpLastName VARCHAR(80),
    Department VARCHAR(30),
    DepartID INT NOT Null
    )
    
                    

    Now while inserting the records into table when we try to put Null values into Not Null specified column it shows an error.

    INSERT INTO EmployeeDetails
    VALUES
    (1001,'Branda','Paul','IT',null)
                    

    SQL Not Null constraint

  3. Unique Constraint

    Unique Constraint is used to enforce the uniqueness on non Primary key columns. A unique constraint is similar to Primary key except that it can have null values unless specified not null.

    Below is the example for Unique Constraint applied on EmpID column of EmployeeDetails table.

        Create table EmployeeDetails 
        (
            EmpID int unique ,
            EmpFirstName varchar(80),
            EmpLastName varchar(80),
            Department varchar(30),
            DepartID int NOT Null
        )
    
                    

    With applying the below query the Constraint type can be identified.

        SELECT CONSTRAINT_NAME,
         TABLE_SCHEMA ,
         TABLE_NAME,
         CONSTRAINT_TYPE
         FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME='EmployeeDetails'
    
                    

    SQL Unique constraint

  4. Check Constraint

    A Check Constraint enforces a restriction on the column for the value to be inserted.

  5. Default Constraint

    A Default Constraint is used to assign a constant value to a column, and user need not insert value for such a column.

    Below is create table query in which column TotalMarks is set as Default to 500 and MarksObtained column check the data to be more than 250.

    CREATE TABLE StudentPassMarks 
    (
        StudName VARCHAR(80),
        TotalMarks INT default 500,
        MarksObtained INT CHECK (marksobtained > 250)
    )
    
                    

    Here after applying the constraints when we try to insert the data, first row gets inserted because MarksObtained are greater than 250.Whereas the second insert command gets failed as MarksObtained are less than 250. As total marks column is specified as default we need not put the data while using insert command.

        INSERT INTO StudentPassMarks (StudName,MarksObtained)
        VALUES
        ('Branda Paul',429)
        INSERT INTO StudentPassMarks (StudName,MarksObtained)
        VALUES
        ('Branda Paul',200)
    
                    

    SQL Server default constraint

  6. Foreign Key Constraint

    A Foreign Key Constraint is used to establish a relationship between two tables where one column is a Primary Key of the table and the other column from other table is referenced to the Primary Key column. A Foreign Key column can also have reference to Unique Key column of another table.

    Below are two tables EmployeeDetails and EmpSalary. EmpID of EmployeeDetails table's Primary Key column and EmpID column of EmpSalary table is Foreign Key which references the EmpID column of EmployeeDetails table.

        CREATE TABLE EmployeeDetails 
        (
            EmpID INT PRIMARY Key ,
            EmpFirstName VARCHAR(80),
            EmpLastName VARCHAR(80),
            Department VARCHAR(30),
            DepartID INT
        )
    
        CREATE TABLE EmpSalary 
        (
            EmpID INT Foreign Key References EmployeeDetails(EmpID) ,
            EmpFullName VARCHAR(80),
            Empsalary INT,
            DepartID INT
        )
    
                    

    SELECT * FROM EmployeeDetails

    SQL foriegn key reference

                        Select * from EmpSalary
                    

    SQL foriegn key reference to other table

    Now if we try to insert another row into EmpSalary table it will raise an error. For more details exception handling see sql exception handling using try catch block.

        INSERT INTO EmpSalary
        VALUES
        (1005,'Petro bond',28000,2)
    
                    

    SQL exception for foreign key

    Cascading constraints can be used while applying Foreign key Constraint

    ON DELETE [NO ACTION | CASCADE | SET NULL | SET DEFAULT ] ON UPDATE [NO ACTION | CASCADE | SET NULL | SET DEFAULT ]

Syntax for adding a constraint

            Alter table <TableName>
            Add Constraint <ConstraintName> <ConstraintType>
        

Dropping a constraint

            Alter table <TableName>
            Drop constraint <ConstraintName>
        

Download Source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com