SQL Basics Tutorial: ADD CONSTRAINT

Structured Query Language (SQL) is a standard language for managing and manipulating databases. One fundamental aspect of SQL is ensuring data integrity and accuracy, which is achieved through constraints. In this tutorial, we’ll cover how to use the ADD CONSTRAINT command to enforce rules on the data in your tables.

What is a Constraint?

A constraint is a rule applied to a column or a set of columns in a table to ensure that the data meets certain criteria. Constraints can be used to:

1. Ensure unique values in a column.
2. Ensure that a column cannot contain NULL values.
3. Enforce referential integrity between tables.
4. Specify default values for a column.
5. Ensure that values in a column adhere to a specified condition.

Types of Constraints

1. PRIMARY KEY: Uniquely identifies each row in a table.
2. FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.
3. UNIQUE: Ensures all values in a column are unique.
4. NOT NULL: Ensures a column cannot have NULL values.
5. CHECK: Ensures the values in a column satisfy a specific condition.
6. DEFAULT: Sets a default value for a column if no value is specified.

Adding Constraints with ALTER TABLE

The ADD CONSTRAINT clause is used with the ALTER TABLE statement to add a constraint to an existing table.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

Examples

1. Adding a PRIMARY KEY Constraint

Scenario

Suppose you have a table named Employees and you want to add a primary key constraint to the EmployeeID column.

Code

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Add PRIMARY KEY constraint
ALTER TABLE Employees
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID);

Explanation

• CREATE TABLE Employees: Creates a table named Employees.
• ALTER TABLE Employees ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID): Alters the table to add a primary key constraint named PK_Employee on the EmployeeID column, ensuring each value in this column is unique and not null.

2. Adding a FOREIGN KEY Constraint

Scenario

You have another table named Departments and you want to link Employees to Departments through a foreign key.

Code

-- Create the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Add a DepartmentID column to Employees
ALTER TABLE Employees
ADD DepartmentID INT;

-- Add FOREIGN KEY constraint
ALTER TABLE Employees
ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)
REFERENCES Departments(DepartmentID);

Explanation

• CREATE TABLE Departments: Creates the Departments table with a primary key on DepartmentID.
• ALTER TABLE Employees ADD DepartmentID INT: Adds the DepartmentID column to the Employees table.
• ALTER TABLE Employees ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID): Adds a foreign key constraint named FK_Department to the DepartmentID column in Employees, linking it to the DepartmentID in Departments, ensuring referential integrity.

3. Adding a UNIQUE Constraint

Scenario

Ensure that the Email column in the Employees table has unique values.

Code

-- Add Email column to Employees
ALTER TABLE Employees
ADD Email VARCHAR(100);

-- Add UNIQUE constraint
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

Explanation

• ALTER TABLE Employees ADD Email VARCHAR(100): Adds the Email column to the Employees table.
• ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email): Adds a unique constraint named UQ_Email on the Email column, ensuring all email addresses are unique.

4. Adding a NOT NULL Constraint

Scenario

Ensure that the LastName column in the Employees table cannot have NULL values.

Code

-- Add NOT NULL constraint
ALTER TABLE Employees
ALTER COLUMN LastName VARCHAR(50) NOT NULL;

Explanation

• ALTER TABLE Employees ALTER COLUMN LastName VARCHAR(50) NOT NULL: Modifies the LastName column to ensure it cannot contain NULL values.

5. Adding a CHECK Constraint

Scenario

Ensure that the Salary column in the Employees table has values greater than 0.

Code

-- Add Salary column to Employees
ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);

-- Add CHECK constraint
ALTER TABLE Employees
ADD CONSTRAINT CHK_Salary CHECK (Salary > 0);

Explanation

• ALTER TABLE Employees ADD Salary DECIMAL(10, 2): Adds the Salary column to the Employees table.
• ALTER TABLE Employees ADD CONSTRAINT CHK_Salary CHECK (Salary > 0): Adds a check constraint named CHK_Salary to ensure the Salary column only contains values greater than 0.

6. Adding a DEFAULT Constraint

Scenario

Ensure that the HireDate column in the Employees table defaults to the current date if no date is provided.

Code

-- Add HireDate column to Employees
ALTER TABLE Employees
ADD HireDate DATE;

-- Add DEFAULT constraint
ALTER TABLE Employees
ADD CONSTRAINT DF_HireDate DEFAULT (GETDATE()) FOR HireDate;

Explanation

• ALTER TABLE Employees ADD HireDate DATE: Adds the HireDate column to the Employees table.
• ALTER TABLE Employees ADD CONSTRAINT DF_HireDate DEFAULT (GETDATE()) FOR HireDate: Adds a default constraint named DF_HireDate to set the default value of HireDate to the current date if no value is provided.

Conclusion

Using the ADD CONSTRAINT command in SQL allows you to enforce various rules on your table columns to ensure data integrity and accuracy. This tutorial covered the basic constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. By understanding and applying these constraints, you can create robust and reliable database schemas.