Constraints are rules that the SQL Server uses to enforce data integrity in tables. SQL Server database engine runs these validations on the column values before it inserts or updates them. Hence helping in maintaining the integrity, accuracy & reliability of the data
Table of Contents
Need for Constraints in SQL Server
The Data types in SQL Server stops you from entering invalid data into a column. For Example, you cannot insert strings into numeric or integer columns.
But that does not prevent users from entering invalid data. For Example, the user may enter a negative number in the salary field. The same employee code may be given to two employees. Users may not provide a value to a required field like name, email, etc.
The Constraints help us prevent those from happening.
List of All SQL Server Constraints
There are six constraints in SQL Server.
- Default Constraint
- NOT NULL Constraint
- Primary Key in SQL Server
- Foreign Key in SQL Server
- Unique Key in SQL Server
- Check Constraint
We create constraints when we create a table. We can also add constraints to the existing table using the Alter Table statement. There are two ways we can add a constraint to a table. Either at the column level or at the table level.
The column level constraints are specified along with the column definition in a create table statement. The following example shows the syntax & example. Note that constraint will apply to the column on which we define it. You cannot refer to other columns in column-level constraints.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --- syntax CREATE TABLE <tableName> ( <column_definition> [<constraint-definition>], [ ,... n ] } ---- Example of column level constraint CREATE TABLE Employee ( ..., ..., Department [varchar](20) Default 'Admin' , ..., ..., ) |
We can also define the constraint at the table level. The basic syntax is shown below. If the constraint spans multiple columns, then defining the constraint at the table level is the only option you have.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- Syntax CREATE TABLE <tableName> ( <column_definision> [ ,... n ] [ <table_constraint> ] [ ,... n ] ) ---- Example of table level constraint CREATE TABLE Employee ( ..., ..., Department [varchar](20), ..., ..., CONSTRAINT DEFAULT 'Admin' FOR Department ) |
Default Constraint
We use the Default Constraint in SQL Server to specify default values for a column. SQL Server uses the default value when the value for that column is absent in the insert query.
Example of default constraint at the column level.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE Employee ( EmployeeID [int] , Name [varchar](50) , Department [varchar](20) Default 'Admin' , ) --- named default constraint. CREATE TABLE Employee ( EmployeeID [int] , Name [varchar](50) , Department [varchar](20) CONSTRAINT DF_Employee_Department DEFAULT 'Admin' ) |
Example of default constraint at the table level.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE Employee ( EmployeeID [int] , Name [varchar](50) , Department [varchar](20) , CONSTRAINT DEFAULT 'Admin' FOR Department ) --- or --- named default constraint CREATE TABLE Employee ( EmployeeID [int] , Name [varchar](50) , Department [varchar](20) , CONSTRAINT DF_Employee_Department DEFAULT 'Admin' FOR Department ) |
Adding default constraint to an existing column.
1 2 3 4 | ALTER TABLE Employee ADD CONSTRAINT DF_Employee_Department DEFAULT '' FOR Department; |
Read more at Default Constraint in SQL Server
NOT NULL Constraint
NOT NULL Constraint in SQL Server specifies that the column cannot store a NULL value. All inserts & updates to the column must specify a value. Attempting to insert or update NULL value will result in the error.
Example of NOT Null constraint
1 2 3 4 5 6 7 | CREATE TABLE Employee ( EmployeeID [int] , Name [varchar](50) , Department [varchar](20) NOT NULL ) |
Read More about NOT NULL Constraint
Primary Key Constraint
A primary key in SQL Server is a field (or combination of fields) in the table that uniquely identifies each row or record
Example of Primary Key Constraint
1 2 3 4 5 6 7 | CREATE TABLE Employee ( EmployeeID [int] PRIMARY KEY NOT NULL, Name [varchar](50) NULL, Department [varchar](20) NULL, ) |
At table level
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID [int] NOT NULL, Name [varchar](50) NULL, Department [varchar](20) NULL, PRIMARY KEY (EmployeeID ) ) |
Composite Primary Key
1 2 3 4 5 6 7 8 | CREATE TABLE EmployeeLeave ( EmployeeID [int] NOT NULL, LeaveID [int] NOT NULL, DateTaken DateTime2] NOT NULL PRIMARY KEY ( EmployeeID, LeaveID ) ) |
Adding Primary key to an existing table.
1 2 3 4 5 6 7 8 | Alter Table EmployeeLeave Add PRIMARY KEY ( EmployeeID, LeaveID ) or Alter Table EmployeeLeave Add CONSTRAINT PK_EmployeeLeave PRIMARY KEY ( EmployeeID, LeaveID ) |
Read More about Primary Key Constraint
Foreign Key Constraint
Foreign key in SQL Server is used to establish the relationship between two tables
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE TABLE Customer ( CustomerID [int] NOT NULL , Name varchar(10) NOT NULL, PRIMARY KEY (CustomerID) ) CREATE TABLE Invoice ( InvoiceID [int] NOT NULL, CustomerID [int] NOT NULL REFERENCES Customer(CustomerID), Amount [decimal](18,2) NOT NULL, PRIMARY KEY (InvoiceID), ) CREATE TABLE Invoice ( InvoiceID [int] NOT NULL, CustomerID [int] NOT NULL , Amount [decimal](18,2) NOT NULL, PRIMARY KEY (InvoiceID), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ) |
Composite Foreign Key
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE TableA ( TableID1 [int] NOT NULL , TableID2 [int] NOT NULL , PRIMARY KEY (TableID1,TableID2) ) CREATE TABLE TableB ( TableBID [int] NOT NULL, ID1 [int] NOT NULL , ID2 [int] NOT NULL , PRIMARY KEY (TableBID), CONSTRAINT FK_TableB_TableA_ID1_ID2 FOREIGN KEY (ID1,ID2) REFERENCES TableA(TableID1,TableID2) ) |
Read More about Foreign Key Constraint
Unique Key Constraint
Unique Constraint or Unique key in SQL Server enforces the uniqueness of the column value. i.e. no two rows of a table can have the same values.
The query to apply Unique Constraint
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | ---Column Level CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , EmailID varchar(50) UNIQUE , Primary Key (EmployeeID) ) --- Table Level CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , EmailID varchar(50) , Primary Key (EmployeeID) , UNIQUE (FirstName) ) |
Composite Unique Key
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID int NOT NULL , FirstName varchar(50) , LastName varchar(50) , Primary Key (EmployeeID) , UNIQUE (FirstName,LastName) ) |
Naming the Unique Key
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE Employee ( EmployeeID int NOT NULL , FirstName varchar(50) , LastName varchar(50) , Primary Key (EmployeeID) , CONSTRAINT UK_FirstName_LastName UNIQUE(FirstName, LastName) ) |
Adding Unique Key to Existing Table
1 2 3 | Alter Table TableB ADD CONSTRAINT UK_FirstName_LastName UNIQUE(FirstName,LastName) |
Read more about Unique Constraint
Check Constraint
The check constraint in SQL Server allows us to validate data values that are being inserted or updated in one or more columns. If the validation fails, then SQL Server will not insert or update the data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- Check Constraint at column level CREATE TABLE Employee ( EmployeeID int , Name varchar(50) CHECK (len(Name) > 15), Salary Decimal(18,2) NOT NULL CHECK (Salary > 0) , Primary Key (EmployeeID), ) -- Check Constraint at Table level CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID), CHECK (len(Name) > 15), CHECK (Salary > 0), ) |
Naming the Constraint
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID), CONSTRAINT CK_Employee_Name CHECK (len(Name) > 15), CONSTRAINT CK_Employee_Salary CHECK (Salary > 0), ) |
Adding check constraint to an existing table
1 2 3 | Alter Table Employee ADD CONSTRAINT CK_Employee Check (Salary > 0) |
Read more about Check Constraint
Query to find All Constraints in SQL Server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | select table_view, object_type, constraint_type, constraint_name, details from ( select schema_name(t.schema_id) + '.' + t.[name] as table_view, case when t.[type] = 'U' then 'Table' when t.[type] = 'V' then 'View' end as [object_type], case when c.[type] = 'PK' then 'Primary key' when c.[type] = 'UQ' then 'Unique constraint' when i.[type] = 1 then 'Unique clustered index' when i.type = 2 then 'Unique index' end as constraint_type, isnull(c.[name], i.[name]) as constraint_name, substring(column_names, 1, len(column_names)-1) as [details] from sys.objects t left outer join sys.indexes i on t.object_id = i.object_id left outer join sys.key_constraints c on i.object_id = c.parent_object_id and i.index_id = c.unique_index_id cross apply (select col.[name] + ', ' from sys.index_columns ic inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id where ic.object_id = t.object_id and ic.index_id = i.index_id order by col.column_id for xml path ('') ) D (column_names) where is_unique = 1 and t.is_ms_shipped <> 1 union all select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table, 'Table', 'Foreign key', fk.name as fk_constraint_name, schema_name(pk_tab.schema_id) + '.' + pk_tab.name from sys.foreign_keys fk inner join sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id inner join sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id inner join sys.foreign_key_columns fk_cols on fk_cols.constraint_object_id = fk.object_id union all select schema_name(t.schema_id) + '.' + t.[name], 'Table', 'Check constraint', con.[name] as constraint_name, con.[definition] from sys.check_constraints con left outer join sys.objects t on con.parent_object_id = t.object_id left outer join sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id union all select schema_name(t.schema_id) + '.' + t.[name], 'Table', 'Default constraint', con.[name], col.[name] + ' = ' + con.[definition] from sys.default_constraints con left outer join sys.objects t on con.parent_object_id = t.object_id left outer join sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id) a order by table_view, constraint_type, constraint_name |
Drop a Constraint
To Drop a constraint first, find out the name of the constant using the query from the previous section and use the Alter table Drop constraint query to delete it.
1 2 3 | Alter table TableB drop CONSTRAINT UK_FirstName |
