SQL Server Table Constraints Tutorial

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

It ensures that the column(s) defined as the primary key contain unique, non-null values.

• Only one primary key per table
• Automatically creates a unique index
• Cannot contain NULL values

FOREIGN KEY Constraint

A FOREIGN KEY constraint enforces a link between data in two tables.

It ensures that the value in the foreign key column matches a value in the referenced primary key column.

• Maintains referential integrity
• Prevents orphan records
• Can cascade updates or deletes

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column or group of columns are distinct.

Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints and columns can accept NULLs (but only one NULL per column).

• Enforces uniqueness
• Allows one NULL value per column
• Can be applied to multiple columns

CHECK Constraint

CHECK constraints validate that data meets a specific condition before it is inserted or updated.

They are useful for enforcing domain integrity rules.

• Can use logical expressions
• Prevents invalid data entry
• Supports complex conditions

DEFAULT Constraint

The DEFAULT constraint assigns a default value to a column when no value is specified during insert.

It helps maintain consistent data without requiring explicit values.

• Provides default values
• Can use constants or functions
• Does not prevent explicit NULLs

Creating a Table with PRIMARY KEY and DEFAULT

This example creates a Customers table with a primary key and a default value for the CreatedDate column.

SQL Code Example

```sql

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

CustomerName NVARCHAR(100) NOT NULL,

CreatedDate DATETIME DEFAULT GETDATE()

);

```

Creating a Table with FOREIGN KEY and CHECK

This example creates an Orders table with a foreign key referencing Customers and a check constraint on Quantity.

SQL Code Example

```sql

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),

Quantity INT CHECK (Quantity > 0)

);

```