PostgreSQL Constraints

PostgreSQL constraints are used to specify rules for data in a table.

If there is any behavior that violates constraints, the behavior will be terminated by the constraint.

Constraints can be specified when the table is created (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 apply to the entire table.

The following are the commonly used constraints in PostgreSQL.

NOT NULL: Indicates that a column cannot store NULL values.
UNIQUE: Ensures that values in a column are all 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, which helps to find a specific record in the table more easily and quickly.
FOREIGN Key: Ensures referential integrity by guaranteeing 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 on the specified columns or expressions using the specified operators, at least one of the operator comparisons will return false or null.

NOT NULL Constraint

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

NULL is not the same as having no data; it represents unknown data.

Example

The following example creates a new table called COMPANY1 with 5 fields, three of which, 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 can set a column to be unique, avoiding duplicate values in the same column.

Example

The following example creates a new table called COMPANY3 with 5 fields. The AGE field 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.

PRIMARY KEY, also called the primary key, is the unique identifier for each record in a data table.

There may be multiple columns set with 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 we can also create relationships between tables by setting the primary key as a foreign key in other tables.

A 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(s), then no two records can have the same value in these fields.

Example

Next, we create the COMAPNY4 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

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

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

Example

The following example creates a COMPANY6 table and adds 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 and adds 3 fields. 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 certain condition, that is, it checks each record being input. If the condition evaluates to false, the record violates the constraint and cannot be entered into the table.

Example

For example, the following example creates a new table COMPANY5 with five columns. Here, we add a CHECK constraint on the SALARY column, so 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 on the specified columns or expressions using the specified operators, 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 the 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 =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表达式返回 true，则不允许插入，否则允许
);

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

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

Since we have enforced that ages must be equal, let's verify 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 );
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入

The first two are successfully added to the COMPANY7 table, but the third will report 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).

Drop Constraints

To drop a constraint, you must know its name. If you already know the name, it is simple to drop the constraint. If you do not know the name, you need to find the system-generated name using\d table_nameYou can find this information.

The general syntax is as follows:

ALTER TABLE table_name DROP CONSTRAINT some_name;

Other Extensions

PostgreSQL Advanced

PostgreSQL Constraints

NOT NULL Constraint

UNIQUE Constraint

PRIMARY KEY

FOREIGN KEY Constraint

CHECK Constraint

EXCLUSION Constraint

Drop Constraints