SQL Programmers Blog - SQL Constraints

A constraint is a rule or condition defined on a table or table column(s) to prevent wrong data from being entered into the database. DML operations like INSERT, UPDATE or DELETE that violate the constraints are not allowed to modify the data and thus the constraints maintain the database integrity (i.e. the accuracy and reliability of the data in the database).

In the absence of constraints, we can not rely on the users and applications to enforce the database integrity, which almost always leads to database inconsistency and in turn causes loss of information. For example, when the integrity of an inventory database is lost, you may find one or more products with same product id, purchase orders generated with duplicate or incorrect product details and other common data inaccuracies.

To simplify, Data Integrity can be categorized into four types. Each type of data integrity can be enforced in a number of different ways using constraints. The four types of data integrity are:

1. Domain Integrity - can be achieved by the rules that validate the entries of a given column for valid type, correct format or a range of possible values.
2. Referential Integrity - prevents deletion of any records in use i.e. used by other records or tables. It ensures that the relationships between the database tables are not affected even after a record is inserted, updated or deleted.
3. Entity Integrity - ensures the uniqueness of the records in a table. In other words, restricts duplicate records to enter into a table.
4. User-Defined Integrity - can be implemented by the user defined rules to adapt business specific rules that do not come under domain, referential and entity integrity.

Now let us look at the constraints supported by SQL server and how they are used to enforce the data integrity. SQL server provides following constraints.

1. PRIMARY KEY Constraints.
2. FOREIGN KEY Constraints.
3. DEFAULT Definitions.
4. NOT NULL Constraints.
5. UNIQUE Constraints.
6. CHECK Constraints.

PRIMARY KEY Constraints

A Primary Key is a column or combination of columns whose values uniquely identify each row in a table. Primary key constraint enforces entity integrity. A table can have only one primary key constraint defined for it and one or more columns can play the primary key role.

The primary key columns do not accept NULL values. If only one column is defined as primary key then duplicate values are not allowed in the column. If two or more columns are defined as primary key altogether then duplicate values are allowed within a column but the combination of values from all columns that take part in primary key constraint are not allowed to repeat in a table (i.e. the combination of values must be unique).

When a primary key constraint is defined for a table, a UNIQUE Index is created on the columns that involve in the primary key constraint to improve the performance when these columns are used in queries and joins.

Now let’s learn how to define a primary key constraint for a table during table creation and table modification. The following script shows various methods to define Primary Key Constraints.

-- Define a primary key constraint without a constraint name during
-- table creation
CREATE TABLE Products
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY
);
GO

-- Drop the table Products
DROP TABLE Products;
GO

-- Define a Primary Key Constraint in the column level
CREATE TABLE Products
(
    ProductID INT CONSTRAINT PK_Products_ProdID PRIMARY KEY,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY
);
GO

-- Drop the table Products
DROP TABLE Products;
GO

-- Define a Primary Key Constraint in the table level
CREATE TABLE Products
(
    ProductID INT,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY,
    CONSTRAINT PK_Products_ProdID PRIMARY KEY(ProductID)
);
GO

-- Drop the table
DROP TABLE Products;
GO

-- Define a table with no primary key constraints.
-- Note that the NOT NULL constraint is mentioned on the ProductID
-- Column this time.
-- Because Primary Key Constraints can not be created on nullable
-- columns.
CREATE TABLE Products
(
    ProductID INT NOT NULL,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY
);
GO

-- Define a Primary Key Constraint after the table is created
ALTER TABLE Products
ADD CONSTRAINT PK_Products_ProdID PRIMARY KEY(ProductID)
GO

-- Deleting a Primary Key Constraint
ALTER TABLE Products
DROP CONSTRAINT PK_Products_ProdID
GO

-- Drop the table
DROP TABLE Products;
GO

-- Define a Primary Key Constraint on muliptle columns
-- during table creation
CREATE TABLE Products
(
    ProductID INT,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY,
    CONSTRAINT PK_Products_ProdID PRIMARY KEY(ProductID, ProductName)
);
GO

-- Drop the table
DROP TABLE Products;
GO

-- Define a Primary Key Constraint on muliptle columns
-- during table creation
CREATE TABLE Products
(
    ProductID INT,
    ProductName VARCHAR(30),
    VendorName VARCHAR(30),
    Price MONEY,
    CONSTRAINT PK_Products_ProdID_ProdName PRIMARY KEY(ProductID, ProductName)
);
GO

-- Drop the table
DROP TABLE Products;
GO

-- Define a table with no Primary Key Constraints.
CREATE TABLE Products
(
    ProductID INT NOT NULL,
    ProductName VARCHAR(30) NOT NULL,
    VendorName VARCHAR(30),
    Price MONEY
);
GO

-- Define a Primary Key Constraint on multiple columns.
ALTER TABLE Products
ADD CONSTRAINT PK_Products_ProdID_ProdName PRIMARY KEY(ProductID, ProductName)
GO

FOREIGN KEY Constraints

A foreign key is a column or a combination of columns that can establish a relationship between two tables. The foreign key column(s) links the data in two tables.

The example below uses two tables namely tblOrders and tblOrderDetails to illustrate the foreign key constraint. In this, the column OrderID acts as a primary key in the tblOrders table, also acts as a foreign key in the tblOrderDetails table which means it creates a relationship or a link between the two tables. A table can have more than one Foreign Key constraint. SQL server does not have any limit for the number of Foreign Key constraints per table.

Here the Foreign Key Constraint enforces the Domain Integrity by ensuring that an OrderID entered into the tblOrderDetails is found in the OrderID column of the tblOrders table. Also, it enforces the Referential Integrity as deletion of an OrderID record from tblOrders is not allowed until all the records that belong to that particular OrderID are deleted from the tblOrderDetails table in order to preserve the relationship between the tables.

The following script shows the different ways to create Foreign Key Constraints.

---- Create Table Statement for the tblOrders table.
CREATE TABLE tblOrders
(
    OrderID INT CONSTRAINT PK_tblOrders_OrderID PRIMARY KEY,
    OrderNo VARCHAR(10),
    OrderDate DATETIME,
    OrderDescription VARCHAR(50)
);
GO Create Table Statement for the tblOrderDetails table.
-- Here, the Foreign Key Constraint is defined along with the column definition.
CREATE TABLE tblOrderDetails
(
    ItemID INT CONSTRAINT PK_tblOrderDetails_ItemID PRIMARY KEY,
    OrderID INT CONSTRAINT FK_tblOrderDetails_OrderID FOREIGN KEY REFERENCES tblOrders(OrderID),
    ItemNo VARCHAR(10),
    ItemDescription VARCHAR(30),
    Quantity INT,
    Price MONEY
);
GO

DROP TABLE tblOrderDetails;
GO

-- Create Table Statement for the tblOrderDetails table.
-- Here, the Foreign Key Constraints is defined after the columns are
-- defined
CREATE TABLE tblOrderDetails
(
    ItemID INT,
    OrderID INT,
    ItemNo VARCHAR(10),
    ItemDescription VARCHAR(30),
    Quantity INT,
    Price MONEY,
    CONSTRAINT PK_tblOrderDetails_ItemID PRIMARY KEY(ItemID),
    CONSTRAINT FK_tblOrderDetails_OrderID FOREIGN KEY(OrderID) REFERENCES tblOrders(OrderID)
);
GO

-- Drop the table tblOrderDetails.
DROP TABLE tblOrderDetails;
GO

-- Create Table Statement for the table tblOrderDetails
-- In this example, first the table will be created later it is altered
-- to create a Foreign Key Constraint.
CREATE TABLE tblOrderDetails
(
    ItemID INT CONSTRAINT PK_tblOrderDetails_ItemID PRIMARY KEY,
    OrderID INT,
    ItemNo VARCHAR(10),
    ItemDescription VARCHAR(30),
    Quantity INT,
    Price MONEY
);
GO

-- Alter the tblOrderDetails table definition to create a
-- Foreign Key Constraint on the OrderID column.
ALTER TABLE tblOrderDetails
ADD CONSTRAINT FK_tblOrderDetails_OrderID FOREIGN KEY(OrderID)
REFERENCES tblOrders(OrderID);
GO

-- Delete a Foreign Key Constraint
ALTER TABLE tblOrderDetails
DROP CONSTRAINT FK_tblOrderDetails_OrderID;
GO

DEFAULT Constraints

In certain scenarios, we may need to populate a column with a pre-defined value if a value for the column is not specified explicitly during INSERT operations. When a default constraint is not defined, if you insert a record into a table without specifying a value for a column then the column may be filled in with NULL values or may result in errors. It may lead to lose the data integrity if a possible and meaningful value is not entered into a table column.

For example, in the example below, the table tblOrders uses an OrderDate Column. Consider that a record is inserted without a valid date for the OrderDate column then Null values will be inserted into the column. Now if you generate a report for the Orders placed for a particular period, say the last 6 months, then this record or order will be ignored and a report with incorrect information will be generated and in turn it becomes an important issue.

One way to fix this issue is to use a default constraint that inserts the current date into the OrderDate column when a date is not provided explicitly in the INSERT statement. This shows how a Default Constraint enforces the Domain integrity by restricting invalid values and providing a permissible value for a table column.

-- Create Table Statement for the tblOrders table.
-- A default constraint is defined for the OrderDate column.
CREATE TABLE tblOrders
(
    OrderID INT CONSTRAINT PK_tblOrders_OrderID PRIMARY KEY,
    OrderNo VARCHAR(10),
    OrderDate DATETIME CONSTRAINT DF_tblOrders_OrderID DEFAULT GETDATE(),
    OrderDescription VARCHAR(50)
);
GO

Now, let us consider that the tblOrders table is already created without a default constraint for the OrderDate column using the following query.

-- Create Table Statement for the tblOrders table.
CREATE TABLE tblOrders
(
    OrderID INT CONSTRAINT PK_tblOrders_OrderID PRIMARY KEY,
    OrderNo VARCHAR(10),
    OrderDate DATETIME,
    OrderDescription VARCHAR(50)
);
GO

To create a Default Constraint for the OrderDate column, the tblOrders table definition should be altered as follows.

-- To add a default constraint to the tblOrders table.
ALTER TABLE tblOrders
ADD CONSTRAINT DF_tblOrders_OrderDate DEFAULT GETDATE() FOR OrderDate
GO

Now to delete a default constraint,

--Delete a Default Constraint.
ALTER TABLE tblOrders
DROP CONSTRAINT DF_tblOrders_OrderDate
GO

NULL Constraints

Null Constraints help us to explicitly set the nullability of a table column. The nullablity of a column decides whether NULL values are allowed in a column or not. By definition, Null Constraint is not a strict constraint but it enforces the Domain Integrity by ensuring the nullability of a column.

For example, in the tblOrders table, the OrderID is a Primary Key. So the NULL values are not allowed to enter this column by default. Next, the OrderDate column has a Default Constraint defined for it. So the NULL values entering this column will be replaced by the default value i.e. current date. Now, the OrderNo column must be restricted for NULL values because placing an order without an OrderNo makes it meaningless and leads to loss of data integrity. But the OrderDescription column can allow NULL values because it can be considered as an optional field.

Now, let us see how to set the nullability of the columns in the tblOrders table. A column disallows NULL values if it is defined with NOT NULL and allows NULL values if it is defined with NULL as shown below.

-- Create Table Statement for the tblOrders table with
-- the nullability mentioned for each column explicitly.
CREATE TABLE tblOrders
(
    OrderID INT NOT NULL,
    OrderNo VARCHAR(10) NOT NULL,
    OrderDate DATETIME NOT NULL CONSTRAINT DF_tblOrders_OrderDate DEFAULT GETDATE(),
    OrderDescription VARCHAR(50) NULL,
    CONSTRAINT PK_tblOrders_OrderID PRIMARY KEY(OrderID)
);
GO

If the nullablity of a column is not explicitly mentioned then SQL Server determines the nullabilty of the column using various rules. Now let us see how to modify a column property to set the nullability. We use the tblOrderDetails table to illustrate this. First we create the tblOrderDetails table without specifying the nullability of the columns. Next we alter the columns’ properties to set the nullability.

-- Create Table Statement for tblOrderDetails
CREATE TABLE tblOrderDetails
(
    ItemID INT,
    OrderID INT,
    ItemNo VARCHAR(10),
    ItemDescription VARCHAR(30),
    Quantity INT,
    Price MONEY,
    CONSTRAINT PK_tblOrderDetails_ItemID PRIMARY KEY(ItemID),
    CONSTRAINT FK_tblOrderDetails_OrderID FOREIGN KEY(OrderID) REFERENCES tblOrders(OrderID),
);
GO

-- To disallow NULL values in the Item No. Column
ALTER TABLE tblOrderDetails
ALTER COLUMN ItemNo VARCHAR(10) NOT NULL
GO

-- To disallow NULL values in the Quantity Column
ALTER TABLE tblOrderDetails
ALTER COLUMN Quantity INT NOT NULL
GO

-- To disallow NULL values in the Price Column
ALTER TABLE tblOrderDetails
ALTER COLUMN Price MONEY NOT NULL
GO

--To allow NULL values in the Item Description column
ALTER TABLE tblOrderDetails
ALTER COLUMN ItemDescription VARCHAR(50) NULL
GO

UNIQUE Constraints

Unique Constraints ensure that there are no duplicate values in the specified column or combination of columns. Generally, Unique Constraint will be created on the columns which do not allow duplicate values and do not participate in Primary Key Constraint. Unlike Primary Key Constraints, multiple Unique Constraints can be created per table and NULL values can be allowed in the columns referenced in the Unique Constraint.

Unique Constraint enforces Entity Integrity like Primary Key Constraint. When a Unique Constraint is created, SQL Server creates a Unique Index to maintain the uniqueness of the columns that involve in the Unique Constraint. Unique Index creates only an index to improve the query performance on the specified Columns. But Unique Constraint creates not only a Unique Index but also indicates the SQL server to enforce a rule on the specified columns.

Next, Let’s consider an example table tblUsers to illustrate the use of Unique Constraints. In this table, the UserID column acts as a Primary Key. Consider the following scenario where each user must be given unique username to log in to the application and each user must enter unique email id so that the password will be emailed to this id if the user forgets his password and requests for it. So Unique Constraint must be created for the EmailID and UserName columns individually.

Now you can use the following query to create the tblUsers along with Unique Constraints for the EmailID and UserName columns.

CREATE TABLE tblUsers
(
    UserID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Gender VARCHAR(1),
    DOB DATE,
    EmailID VARCHAR(30) NOT NULL UNIQUE,
    UserName VARCHAR(10) NOT NULL UNIQUE,
    [Password] VARCHAR(15) NOT NULL,
);
GO

The above CREATE TABLE statement for tblUsers table creates un-named Primary Key and Unique Constraints which means the SQL Server creates them with randomly generated names. As mentioned earlier, a Unique Index will be created when a Unique Constraint is created.

CREATE TABLE tblUsers
(

    UserID INT NOT NULL CONSTRAINT PK_tblUsers_UserID PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Gender VARCHAR(1),
    DOB DATE,
    EmailID VARCHAR(30) NOT NULL CONSTRAINT UQ_tblUsers_EmailID UNIQUE NONCLUSTERED,
    UserName VARCHAR(10) NOT NULL CONSTRAINT UQ_tblUsers_UserName UNIQUE NONCLUSTERED,
    [Password] VARCHAR(15) NOT NULL,
);
GO

We can explicitly mention the type of Index to be created for the Unique Constraint. By default, SQL Server uses Clustered Index for Primary Key Constraint and Non-Clustered indexes for Unique Constraints. However if you mention to create a Unique Constraint with Clustered Index using CLUSTERED keyword immediately after the UNIQUE keyword  then the Primary Key Constraint of the table, if any, will use a Non-Clustered Index.

In the above two CREATE TABLE statements, the Constraints are defined at column levels. Now let’s define the constraints at the table level.

CREATE TABLE tblUsers
(
    UserID INT NOT NULL,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Gender VARCHAR(1),
    DOB DATE,
    EmailID VARCHAR(30) NOT NULL,
    UserName VARCHAR(10) NOT NULL,
    [Password] VARCHAR(15) NOT NULL,
    CONSTRAINT PK_tblUsers_UserID PRIMARY KEY(UserID),
    CONSTRAINT UQ_tblUsers_UserName UNIQUE NONCLUSTERED(UserName),
    CONSTRAINT UQ_tblUsers_EmailID UNIQUE(EmailID)
);
GO

Next let’s see how we can alter an existing table to use Unique Constraints. First, we are going to create the tblUsers without any Unique Constraints defined using the following query.

CREATE TABLE tblUsers
(
    UserID INT NOT NULL CONSTRAINT PK_tblUsers_UserID PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Gender VARCHAR(1),
    DOB DATE,
    EmailID VARCHAR(30) NOT NULL,
    UserName VARCHAR(10) NOT NULL,
    [Password] VARCHAR(15) NOT NULL,
);
GO

Now the following statements will add Unique Constraints to the tblUsers table.

ALTER TABLE tblUsers
ADD CONSTRAINT UQ_tblUsers_UserName UNIQUE NONCLUSTERED(UserName);
GO

ALTER TABLE tblUsers
ADD CONSTRAINT UQ_tblUsers_EmailID UNIQUE(EmailID);
GO

The following query shows you how to delete Unique Constraints from the tblUsers table.

ALTER TABLE tblUsers
DROP CONSTRAINT UQ_tblUsers_UserName;
GO

ALTER TABLE tblUsers
DROP CONSTRAINT UQ_tblUsers_EmailID;
GO

Check Constraints

Check Constraint evaluates a logical expression while inserting or updating a record in a table. The insert or update operation succeeds when the values inserted or updated satisfy the logical expression. Like the Foreign Key Constraints, the Check Constraints enforce the Domain Integrity by limiting the values that can be placed in a column. But they differ in the way of identifying where a value is valid or not.

Foreign Key Constraint gets a list of valid or permissible values from other table. Check constraint uses a logical expression to examine the values. A limitation is that you can not use the columns of other tables in your logical expression.

Let’s consider the tblMarks table which is used to store the marks scored out of 100 by the students in two subjects. The maximum mark scored can be centum (i.e. 100) and the minimum mark scored can be zero. Let’s learn how Check Constraints can be used to validate that the marks entered range between 0 and 100.

The CREATE TABLE statement below defines two Check Constraints at column level. If you define a Check Constraint at column level, the Check Constraint can use only the particular column in the Check Constraint and you cannot use other columns of the same table or columns from another table.

 
CREATE TABLE tblMarks
(
    RollNo INT NOT NULL PRIMARY KEY,
    Subject1 INT NOT NULL CONSTRAINT CK_tblMarks_Subject1 CHECK( Subject1 >= 0 AND Subject1 <= 100),
    Subject2 INT NOT NULL CONSTRAINT CK_tblMarks_Subject2 CHECK( Subject2 >= 0 AND Subject2 <= 100)
);
GO

The above example defines two constraints for subject1 and subject2 columns. The Check Constraint defined for subject1 cannot refer to other columns except subject1 and the same applies to the constraint defined for the subject2 column.

If you define a Check Constraint at the table level then you can use multiple columns of the same table to form the logical expression. The example below uses both subject1 and subject2 in a single constraint and it performs the same checks done by the two constraints defined at the column level in the above example.

CREATE TABLE tblMarks
(
    RollNo INT NOT NULL,
    Subject1 INT NOT NULL,
    Subject2 INT NOT NULL,
    CONSTRAINT PK_tblMarks_RollNo PRIMARY KEY(RollNo),
    CONSTRAINT CK_tblMarks_Subjects CHECK( Subject1 >= 0 AND Subject1 <= 100 AND Subject2 >=0 AND Subject2 <=100)
);
GO

Now let’s learn how we can add a check constraint to an existing table. The CREATE TABLE statement creates the tblMarks table without any Check Constraints. The ALTER TABLE statement adds a Check Constraint to the tblMarks table.

CREATE TABLE tblMarks
(
    RollNo INT NOT NULL,
    Subject1 INT NOT NULL,
    Subject2 INT NOT NULL,
    CONSTRAINT PK_tblMarks_RollNo PRIMARY KEY(RollNo)   
);
GO

ALTER TABLE tblMarks
ADD CONSTRAINT CK_tblMarks_subjects CHECK(subject1 >= 0 AND subject2 >= 0 AND subject1 <= 100 AND subject2 <= 100);
GO

The following query shows you how to delete a Check Constraint from the tblMarks table.

ALTER TABLE tblMarks
DROP CONSTRAINT CK_tblMarks_subjects;
GO

In this article, we came across different types of Data Integrity and studied how these types of integrity are enforced using constraints. We learned how to use T-SQL to implement constraints. Finally, though there are other database features like triggers, rules, etc. (using which you can realize the same functionalities provided by the constraints), Constraints are the preferred method to enforce Data Integrity because they are built-in mechanisms, use less overhead, and perform faster than others.