SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Constraints

Back to: SQL Server Tutorial

In Microsoft SQL Server (MS SQL), constraints are used to enforce rules and maintain data integrity within your tables. Here’s a detailed look at the types of constraints available in MS SQL and how to use them:

1. PRIMARY KEY

  • Purpose: Uniquely identifies each record in a table. Each table can have only one primary key, which can consist of one or more columns.
  • Characteristics: No duplicate values, no NULL values.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, LastName NVARCHAR(255), FirstName NVARCHAR(255) );

2. FOREIGN KEY

  • Purpose: Ensures that the value in a column or set of columns matches values in a column of another table, enforcing referential integrity.
  • Characteristics: Maintains the relationship between tables. Can be set to restrict, cascade, or set NULL on updates/deletes.
CREATE TABLE Orders ( OrderID INT PRIMARY KEY, EmployeeID INT, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) );
  • Referential Actions:
    • ON DELETE CASCADE: Automatically deletes related rows in child tables when a row in the parent table is deleted.
    • ON DELETE SET NULL: Sets the foreign key column to NULL in the child table when a row in the parent table is deleted.
    • ON UPDATE CASCADE: Automatically updates related rows in child tables when the primary key value in the parent table is updated.

3. UNIQUE

  • Purpose: Ensures that all values in a column or a set of columns are unique. Multiple UNIQUE constraints can be defined on a table.
  • Characteristics: Allows NULL values, but multiple NULLs are considered as unique.
CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductCode NVARCHAR(50) UNIQUE, ProductName NVARCHAR(255) );

4. NOT NULL

  • Purpose: Ensures that a column cannot have NULL values. This constraint is often used to ensure that certain columns always contain a value.
  • Characteristics: Enforces that data is always present.
CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName NVARCHAR(255) NOT NULL, LastName NVARCHAR(255) NOT NULL );

5. CHECK

  • Purpose: Ensures that values in a column meet a specific condition or criteria.
  • Characteristics: Enforces domain integrity by restricting the values.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Age INT CHECK (Age >= 18), Salary DECIMAL(10, 2) CHECK (Salary > 0) );

6. DEFAULT

  • Purpose: Provides a default value for a column when no value is specified during insertion.
  • Characteristics: Automatically assigns a default value if none is provided.
ALTER TABLE Employees ALTER COLUMN LastName NVARCHAR(100) NOT NULL;

7. INDEX

  • Purpose: While not a constraint, indexes improve the performance of query operations by speeding up data retrieval. They can be created on one or more columns.
  • Characteristics: Can be clustered or non-clustered.
CREATE INDEX idx_lastname ON Employees (LastName);

Example of Combining Constraints

Here’s an example of a table definition using multiple constraints:

CREATE TABLE Users ( UserID INT PRIMARY KEY, Email NVARCHAR(255) UNIQUE, Age INT CHECK (Age >= 18), CreatedDate DATETIME DEFAULT GETDATE() );

Modifying Constraints

  • Adding a constraint:
ALTER TABLE Employees ADD CONSTRAINT CHK_Age CHECK (Age >= 18);
  • Dropping a constraint:
ALTER TABLE Employees DROP CONSTRAINT CHK_Age;

Constraints are essential for maintaining data quality and enforcing business rules in SQL Server databases. They help ensure that the data adheres to defined rules and relationships, providing a robust framework for data integrity.

Scroll to Top