Postgresql Constraints

PostgreSQL Constraints

PostgreSQL constraints are used to define rules for data in tables.

If any data violates the constraints, the operation is terminated by the constraint.

Constraints can be defined when creating a table (via the CREATE TABLE statement) or after the table is created (via the ALTER TABLE statement).

Constraints ensure the accuracy and reliability of data in the database.

Constraints can be column-level or table-level. Column-level constraints apply only to a column, while table-level constraints are applied to the entire table.

Here are the commonly used constraints in PostgreSQL:

NOT NULL: Indicates that a column cannot store NULL values.
UNIQUE: Ensures that all values in a column are unique.
PRIMARY KEY: A combination of NOT NULL and UNIQUE. Ensures that a column (or a combination of two or more columns) has a unique identifier, making it easier and faster to find a specific record in the table.
FOREIGN KEY: Enforces referential integrity by ensuring that data in one table matches values in another table.
CHECK: Ensures that the values in a column meet a specified condition.
EXCLUSION: An exclusion constraint ensures that if any two rows are compared using a specified operator on specified columns or expressions, at least one of the operator comparisons will return false or null.

NOT NULL Constraint

By default, a column can store NULL values. If you do not want a column to have NULL values, you need to define this constraint on the column, specifying that NULL values are not allowed on that column.

NULL is different from no data; it represents unknown data.

Example

The following example creates a new table called COMPANY1 with 5 fields, where three fields ID, NAME, and AGE are set to not accept NULL values:

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are unique, preventing duplicate values in the same column.

Example

The following example creates a new table called COMPANY3 with 5 fields, where AGE is set to UNIQUE, so you cannot add two records with the same age:

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL DEFAULT 50000.00
);

PRIMARY KEY

When designing a database, the PRIMARY KEY is very important.

The PRIMARY KEY, also known as the primary key, is a unique identifier for each record in a table.

There can be multiple columns set as UNIQUE, but only one column in a table can be set as the PRIMARY KEY.

We can use the primary key to reference rows in a table, and also create relationships between tables by setting the primary key as a foreign key in another table.

The primary key is a combination of a NOT NULL constraint and a UNIQUE constraint.

A table can have only one primary key, which can consist of one or more fields. When multiple fields are used as the primary key, they are called a composite key.

If a table defines a primary key on any field, then no two records can have the same value in those fields.

Example

Below we create the COMPANY4 table with ID as the primary key:

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

FOREIGN KEY Constraint

A FOREIGN KEY, also known as a foreign key constraint, specifies that the values in a column (or a set of columns) must match the values in a row of another table.

Typically, a FOREIGN KEY in one table points to a UNIQUE KEY (a key with a UNIQUE constraint) in another table, thereby maintaining referential integrity between the two related tables.

Example

The following example creates a COMPANY6 table with 5 fields:

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

The following example creates a DEPARTMENT1 table with 3 fields, where EMP_ID is the foreign key referencing the ID of COMPANY6:

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT references COMPANY6(ID)
);

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition, meaning each inserted record is checked. If the condition evaluates to false, the record violates the constraint and cannot be inserted into the table.

Example

For example, the following example creates a new table COMPANY5 with five columns. Here, we add a CHECK constraint to the SALARY column so that the salary cannot be zero:

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL CHECK(SALARY > 0)
);

EXCLUSION Constraint

The EXCLUSION constraint ensures that if any two rows are compared using a specified operator on specified columns or expressions, at least one of the operator comparisons will return false or null.

Example

The following example creates a COMPANY7 table with 5 fields and uses an EXCLUDE constraint:

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist (NAME WITH =, -- If NAME is the same and AGE is different, insertion is not allowed; otherwise, insertion is allowed
                       AGE WITH ) -- The comparison result is that if the entire expression returns true, insertion is not allowed; otherwise, it is allowed
);

Here, USING gist is a type of index used for building and executing the constraint.

You need to run the CREATE EXTENSION btree_gist command once for each database. This will install the btree_gist extension, which defines the EXCLUDE constraint for pure scalar data types.

Since we have enforced that the age must be the same, let's see this by inserting records into the table:

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
-- This record has the same NAME as the first one and the same AGE, so it meets the insertion condition
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- This record has the same NAME as the above records but a different AGE, so insertion is not allowed

The first two records are successfully added to the COMPANY7 table, but the third one will produce an error:

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Dropping a Constraint

To drop a constraint, you must know the constraint name. If you know the name, dropping the constraint is simple. If you don't know the name, you need to find the system-generated name. You can use d table_name to find this information.

The general syntax is as follows:

ALTER TABLE table_name DROP CONSTRAINT some_name;

YouTip