What are PRIMARY KEY and FOREIGN KEY constraints?

A PRIMARY KEY uniquely identifies each row in a table, and a FOREIGN KEY makes a column refer to another table's primary key. A primary key can't be NULL, it must be unique, and a table has only one. SQL Server enforces it with a unique index, clustered by default when the table has no clustered index yet.

A foreign key rejects a value with no matching primary key in the parent table, though NULL is allowed. By default, deleting a parent row that still has child rows fails with an error. With ON DELETE CASCADE, the child rows are deleted too, and with ON DELETE SET NULL, their foreign key becomes NULL.

Example:

CREATE TABLE Departments (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    DeptID INT FOREIGN KEY REFERENCES Departments(ID)
);