SQL Constraints

Table of Contents

SQL Constraints

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
7. SQL NOT NULL Constraint
By default, a table column can hold NULL values.

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the P_Id column and the LastName column to not accept NULL values:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Errors

ERROR: constraint "fk_user_number" for relation "phone_user" already exists.

Creating constraints can be an expensive operation on a table containing lots of data. We only want to create the constraints once.

For Postgres,

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 from pg_constraint WHERE
                    conname = 'your-constraint-name' AND
                    conrelid = 'your-table-name'::regclass)
    THEN
        ALTER TABLE your-schema-name.your-table-name ADD CONSTRAINT your-constraint-name your-constraint-definition;
    END IF;
END$$;

This might work. I haven’t tested it.

DO $$
BEGIN
    IF NOT EXISTS (SELECT  constraint_schema, constraint_name
                   FROM information_schema.check_constraints
                   WHERE   constraint_schema = 'myschema'
                   AND   constraint_name = 'myconstraintname')
    THEN
        ALTER TABLE myschema.mytable ADD CONSTRAINT myconstraintname CHECK (column <= 100);
    END IF;
END$$;

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the “P_Id” column when the “Persons” table is created. The CHECK constraint specifies that the column “P_Id” must only include integers greater than 0.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the “City” column when the “Persons” table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Links to this note

Databases