SQL Constraints

What is SQL Constraints ?

When you’re creating a relational database tables, you often want to impose certain restrictions or have rules on some columns to maintain the integrity and accuracy of that column’s data.. If there is any violation between the constraint and the data action, the action is aborted.
Constraints in SQL Server can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared at the table level. At table level constraints, rules will be applied to more than one column in the specified table.

Why Use SQL Constraints?
SQL constraints ensures the following

Data is correct (appropriate to the column).
The applications that input the data must follow the rules set while creating constraints.
We save time spent checking that the data has the required correctness. Manual corrections can be reduced.
Constraints ensures if INSERTs or UPDATEs do not conform to the rules, they will not be added to our database tables.

Constraints are of two types :

Column level constraints: Limits constraint for one specific column only.
Table level constraints: we can define constraint for multiple columns.

1. Column constraint is one or more of the following:

UNIQUE [WITH constraint_with_clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES [schema.]table_name[(column_name)][WITH constraint_with_clause]

Example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
age INTEGER CHECK (age > 0));

2. Table constraint is one or more of the following:

UNIQUE (column_name {, column_name}) [WITH constraint_with_clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})][WITH constraint_with_clause]

Example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));

Following are the six main constraints that are commonly used in SQL Server.

1. NOT NULL :

By default, the columns are able to hold NULL values.

A NOT NULL constraint is used to prevent inserting NULL values into the specified column.

Consider a table with two columns, ID and Name. In the ID column definition statement, the NOT NULL column-level constraint is enforced, considering the ID column as a mandatory column that should be provided with a valid NOT NULL value.

Example :

USE [Test_Db]
GO
CREATE TABLE NullConstraintDemo
(
ID INT NOT NULL,
[Name] VARCHAR(50) NULL
)

INSERT INTO NullConstraintDemo ([ID],[NAME]) VALUES (1,'TestName')
GO
INSERT INTO NullConstraintDemo ([ID],[NAME]) VALUES (null, 2)
GO

Results :

First Insert statement will execute successfully.
Second statement will throw the error, since it is enforcing the NOT NULL constraint rules.

2. UNIQUE Constraint :

The UNIQUE constraint in SQL is used to ensure that no duplicate values will be inserted into a specific column or combination of columns that are participating in the UNIQUE constraint and not part of the PRIMARY KEY.
UNIQUE constraint allows to insert only one unique NULL value to the column, if the column allows NULL.
You can create a UNIQUE constraint on one or more columns of a table

Consider an example of Unique constraint

USE [Test_Db]
GO
CREATE TABLE UniqueConstraintTbl
(
ID INT UNIQUE,
Name VARCHAR(50) NULL
)

Try to insert the below records

INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (1,'Amit')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (2,'Amit')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (NULL,'Steve')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (1,'Ravi')
GO

Results :
Since the unique constraint is applied for the column ID,

first two records will be inserted successfully, as the ID is unique.
The third record will be inserted successfully too, as the unique ID column allows only one NULL value.
Fourth record will fail as 1 is also inserted and exist in the table. ID column doesn’t allow duplicate values

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object can be easily used to retrieve information about all defined constraints in a specific table using the T-SQL script below:

If you want to drop the constraint which was added, that can be done using the statement.

We have got the constraint name from the above T-Sql script, Constraint_Name = "UQ__UniqueCo__3214EC2670E86903", using this name we can write the drop T-Sql statement below

ALTER TABLE UniqueConstraintTbl DROP CONSTRAINT [UQ__UniqueCo__3214EC2670E86903]

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint consists of one column or multiple columns with values that uniquely identify each row in the table.
The PRIMARY KEY constraint combines between the UNIQUE and SQL NOT NULL constraints, where the column or set of columns that are participating in the PRIMARY KEY cannot accept a NULL value.
If the PRIMARY KEY is defined in multiple columns, the combination values of all PRIMARY KEY columns must be unique. However, you can insert duplicate values on each column individually but not on the combination.
A table can have only ONE primary key; and in the table, the primary key can consist of single or multiple columns

Take into consideration that you can define only one PRIMARY KEY per each table,

USE [Test_Db]
GO
CREATE TABLE [dbo].[PrimaryKeyConstraintTbl]
(
     ID INT PRIMARY KEY,
   Name VARCHAR(50) NULL
)

Try to insert the below records :

INSERT INTO [PrimaryKeyConstraintTbl] ([ID],[NAME]) VALUES (1,'Amit')
GO
INSERT INTO [PrimaryKeyConstraintTbl] ([NAME]) VALUES ('Varun')
GO
INSERT INTO [PrimaryKeyConstraintTbl] ([ID],[NAME]) VALUES (1,'Raj')
GO

Results :

First record will be inserted successfully as both the ID and Name values are valid
Second insert will fail, as the ID column is mandatory and cannot be NULL, as the ID column is the PRIMARY KEY
The last INSERT statement will fail too as the provided ID value already exists and the duplicate values are not allowed in the PRIMARY KEY

4. FOREIGN KEY Constraint

A Foreign Key is a database key that is used to link two tables together.
A FOREIGN KEY is a columns (or set of columns) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
You can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table.

The relationship between the child and the parent tables is maintained by checking the existence of the child table FOREIGN KEY values in the referenced parent table’s PRIMARY KEY before inserting these values into the child table. In this way, the FOREIGN KEY constraint, in the child table that references the PRIMARY KEY in the parent table, will enforce database referential integrity. Referential integrity ensures that the relationship between the database tables is preserved during the data insertion process.

The FOREIGN KEY constraint provides you also with the ability to control what action will be taken when the referenced value in the parent table is updated or deleted, using the ON UPDATE and ON DELETE clauses. The supported actions that can be taken when deleting or updating the parent table’s values include:

NO ACTION: When the ON UPDATE or ON DELETE clauses are set to NO ACTION, the performed update or delete operation in the parent table will fail with an error.
CASCADE: Setting the ON UPDATE or ON DELETE clauses to CASCADE, the same action performed on the referenced values of the parent table will be reflected to the related values in the child table. For example, if the referenced value is deleted in the parent table, all related rows in the child table are also deleted.
SET NULL: With this ON UPDATE and ON DELETE clauses option, if the referenced values in the parent table are deleted or modified, all related values in the child table are set to NULL value.
SET DEFAULT: Using the SET DEFAULT option of the ON UPDATE and ON DELETE clauses specifies that, if the referenced values in the parent table are updated or deleted, the related values in the child table with FOREIGN KEY columns will be set to its default value.

We will create two new tables to understand the FOREIGN KEY constraint functionality. The first table will act as the parent table with the ID column defined as a PRIMARY KEY column. The second table will act as the child table, with the ID column defined as the FOREIGN KEY column that references the ID column on the parent table. Following is the script for creating the tables:

USE [Test_Db]
GO
CREATE TABLE ConstraintParentTbl
(
       ID INT PRIMARY KEY,
	 Name VARCHAR(50) NULL
)
GO
CREATE TABLE ConstraintChildTbl
(
        CID INT PRIMARY KEY,
	 ID INT FOREIGN KEY REFERENCES ConstraintParentTbl(ID)
)

Insert the following records

INSERT INTO ConstraintParentTbl ([ID],[NAME]) VALUES (1,'Amit'),(2,'Sanya'),(3,'Sam')
GO
INSERT INTO ConstraintChildTbl (CID,ID) VALUES (1,1)
GO
INSERT INTO ConstraintChildTbl (CID,ID) VALUES (2,4)
GO

Results :

The first record that we tried to insert into the child table is inserted without any error as the ID value of 1 already exists in the parent table.
Second record into the child table will fail because the ID value of 4 doesn’t exist in the parent table, and due to the FOREIGN KEY constraint, you will not be able to insert an ID value to the child table that doesn’t exist in the parent table:

Select the values from parent and child table, only one value will be inserted into child table as below

5. CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
The CHECK constraint will be evaluated during insert/update values, where the value that satisfies the condition will be inserted into the table, otherwise, the insert operation will be discarded
It is allowed to specify multiple CHECK constraints for the same column.
The CHECK constraint is used mainly to enforce the domain integrity by limiting the inserted values to the ones that follow the defined values, range or format rules.

Let us create a new simple table that has three columns; the ID column that is considered as the PRIMARY KEY of that table, Name, and Salary. A CHECK constraint is defined on the Age column to make sure that above 18 values are inserted into that column. The CHECK constraint is defined within CREATE TABLE T-SQL statement below:

CREATE TABLE CheckConstraintTbl
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50) NULL, 
     Age INT CHECK (Age>18)
)
GO

Insert the below records :

INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (1,'Amit',19)
GO
INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (2,'Sagar',18)
GO
INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (3,'Raj',Null)
GO

Results :

first record is inserted with no error as the Age value meets the checking condition.

The second and third INSERT statements will fail, as the provided Salary values do not meet the CHECK constraint condition as you can see in the following error message:

To check the constraints created for the table and name of the constraint, select table constraints from INFORMATION_SCHEMA. Based on the name we can drop the constraint, if required.

6. DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
The default value can be a constant value, a system function value or NULL.

In the below CREATE TABLE statement for a simple table with three columns, a DEFAULT constraint is defined on the ModifiedDate column, that assigns the GETDATE() system function value for that column in case we miss specifying it in the INSERT statement

CREATE TABLE DefaultConstraintTbl
(
        ID INT PRIMARY KEY,
	[Name] VARCHAR(50) NULL,
	ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO

INSERT INTO DefaultConstraintTbl ([ID],[NAME],ModifiedDate) VALUES (1,'Amit',

'2016/10/22')
GO
INSERT INTO DefaultConstraintTbl ([ID],[NAME]) VALUES (2,'Ajay')
GO

Check the values in the table

SQL Constraints

Post a Comment

Contact Form