LuisLlamas Logo
restricciones-constraints-sql-server

Constraints in SQL Server

  • 5 min
  • Intermediate

Constraints are rules that the database engine automatically applies to ensure Data Integrity.

A database is not a simple bucket where we throw information wildly. If it were, we would end up with negative prices, birthdates in the future, or duplicate users.

To prevent this from happening, SQL Server provides us with Constraints. If an INSERT or UPDATE statement violates one of these rules, SQL Server will stop the operation and return an error.

It is the first and most important line of defense for our application. Let’s look at the five essential ones.

PRIMARY KEY

We’ve already talked about this one, but it’s so important it deserves a review. The Primary Key (PK) is the column (or set of columns) that uniquely identifies each row in the table.

  • Does not allow NULL values.
  • Does not allow duplicate values.
  • There can only be one per table.
CREATE TABLE Clientes (
    ClienteID INT PRIMARY KEY, -- Short form
    Nombre NVARCHAR(100)
);
Copied!

Internally, when creating a Primary Key, SQL Server automatically creates a Clustered Index (by default), which physically orders the data on disk according to this key to speed up searches.

FOREIGN KEY

The Foreign Key (FK) is responsible for maintaining Referential Integrity. It is the constraint that ensures the value in a column matches an existing value in the Primary Key of another table.

Basically, it prevents us from creating “orphaned data”.

  • You cannot create an Order for a non-existent customer.
  • You cannot delete a Customer if they have Orders (unless you configure cascade actions).
CREATE TABLE Pedidos (
    PedidoID INT PRIMARY KEY,
    ClienteID INT,
    -- FK constraint definition
    CONSTRAINT FK_Pedidos_Clientes FOREIGN KEY (ClienteID) 
    REFERENCES Clientes(ClienteID)
);
Copied!

Get used to naming your constraints (like FK_Pedidos_Clientes). If you don’t, SQL Server will assign a horrible random name (like FK__Pedidos__Clie__1BFD2C07), and it will be a nightmare to manage in the future.

UNIQUE

Sometimes we need a column to be unique, but it’s not the primary key (classic examples are Email or ID Number).

The UNIQUE constraint ensures that no two rows have the same value in that column.

Differences with Primary Key:

  1. You can have multiple UNIQUE constraints in the same table.
  2. The UNIQUE constraint allows one NULL value (in SQL Server), while the PK allows none.
CREATE TABLE Usuarios (
    UsuarioID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE, -- Cannot have two identical emails
    DNI VARCHAR(20) CONSTRAINT UQ_Usuarios_DNI UNIQUE -- With an explicit name
);
Copied!

CHECK

The CHECK constraint is one of the least known but most powerful. It allows us to define a logical expression (boolean) that each row must satisfy to be valid.

CREATE TABLE Productos (
    ProductoID INT PRIMARY KEY,
    Precio DECIMAL(10, 2),
    Stock INT,
    
    CONSTRAINT CK_Precio_Positivo CHECK (Precio > 0),
    CONSTRAINT CK_Stock_NoNegativo CHECK (Stock >= 0)
);
Copied!

It’s perfect for simple business rules:

  • “Price must be greater than zero”.
  • “Age must be greater than or equal to 18”.
  • “Status must be ‘Active’, ‘Paused’, or ‘Deleted’”.

DEFAULT

Although technically it doesn’t restrict what data enters, the DEFAULT constraint is very important for integrity. It defines what value will be saved in a column if we don’t specify one when doing the INSERT.

CREATE TABLE LogEventos (
    EventoID INT PRIMARY KEY,
    Mensaje NVARCHAR(MAX),
    FechaEvento DATETIME DEFAULT GETDATE(), -- If not specified, sets the current date
    Procesado BIT DEFAULT 0                 -- By default, not processed (false)
);
Copied!

It’s very useful for:

  • Creation dates (GETDATE()).
  • Initial states (e.g., Activo = 1).
  • Setting numeric values to zero instead of NULL.

Complete Example

Let’s see what a table looks like that uses all these constraints together with good naming.

CREATE TABLE Empleados (
    -- 1. PRIMARY KEY with IDENTITY
    EmpleadoID INT IDENTITY(1,1),
    
    -- Nullability constraint (NOT NULL)
    Nombre NVARCHAR(50) NOT NULL,
    Apellido NVARCHAR(50) NOT NULL,
    
    -- 2. UNIQUE: The corporate email cannot be repeated
    Email VARCHAR(100) NOT NULL,
    
    -- 3. CHECK: Salary must be logical and age within working limits
    Salario DECIMAL(10, 2),
    Edad INT,
    
    -- 4. DEFAULT: If we don't say anything, the employee is active upon creation
    Activo BIT CONSTRAINT DF_Empleados_Activo DEFAULT 1,
    FechaContratacion DATE DEFAULT GETDATE(),
    
    -- Named constraint definitions at the end of the table
    CONSTRAINT PK_Empleados PRIMARY KEY (EmpleadoID),
    CONSTRAINT UQ_Empleados_Email UNIQUE (Email),
    CONSTRAINT CK_Empleados_Salario CHECK (Salario > 0),
    CONSTRAINT CK_Empleados_Edad CHECK (Edad >= 16)
);
Copied!

By defining the table this way, we are creating a self-protected structure. It doesn’t matter if the application programmer makes a mistake and tries to enter a negative salary; the database will reject the data and protect the business (and your job 😉).

On this page