PostgreSQL Constraints

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

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

Constraints can be specified when creating a table (through the CREATE TABLE statement), or specified after the table is created (through 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 only apply to columns, and table-level constraints are applied to the entire table.

The following are commonly used constraints in PostgreSQL.

• NOT NULL: Indicates that a column cannot store NULL values.

• UNIQUE: Ensures that the values of 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 of data between one table and another table.

• CHECK: Ensures that the values in the column meet the specified conditions.

• EXCLUSION: Exclusive constraint, ensures that if any two rows of specified columns or expressions are compared using a specified operator, at least one comparison will return false or an empty value.

NOT NULL constraint

By default, columns can be saved as 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 in this column.

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

example

The following example creates a new table called COMPANY1, added 5 There are several fields, among which three IDs, NAME, and AGE are set to not accept nulls:

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

UNIQUE constraint

UNIQUE constraint can be set to make a column unique, avoiding duplicate values in the same column.

example

The following example creates a new table called COMPANY3, added 5 There are several fields, among which 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, PRIMARY KEY is very important.

PRIMARY KEY is known as the primary key, which is the unique identifier for each record in the data table.

Columns that can be set to UNIQUE may have multiple, but only one column in a table can be set as PRIMARY KEY.

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

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

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

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

example

Next, we create COMAPNY4 table where ID is 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 constraint, also known as a foreign key constraint, specifies that the values in a column (or a set of columns) must match the values appearing in a row of another table.

Foreign keys in a table usually point to a UNIQUE KEY in another table (i.e., a unique constraint key), which maintains referential integrity between two related tables.

example

The following example creates a COMPANY6 table and added5fields:

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

For example, the following example creates a table DEPARTMENT1 table and added 3 fields, EMP_ID is a foreign key, referencing the COMPANY6 The ID:

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

CHECK constraint

CHECK constraint ensures that all values in the column satisfy a certain condition, that is, to check the input of a record. If the condition value is false, the record violates the constraint and cannot be input into the table.

example

For example, the following example creates a new table COMPANY5An additional five columns were added. Here, we added a CHECK 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

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

example

The following example creates a COMPANY7 table, add 5 fields, and used EXCLUDE constraints.

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

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

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

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

INSERT INTO COMPANY7　VALUES(1, 'Paul',　32, 'California',　20000.00 );
INSERT INTO COMPANY7　VALUES(2, 'Paul',　32, 'Texas',　20000.00 );　　
--　The NAME of this data is the same as the first one, and the AGE is also the same as the first one, so it meets the insertion conditions
INSERT INTO COMPANY7　VALUES(3, 'Allen',　42, 'California',　20000.00 );
--　This data is the same as the above data's NAME, but the AGE is different, so it is not allowed to be inserted

The first two COMPANIES added successfully7 in the table, but the third one will report an error:

ERROR: duplicate key value violates unique constraint "company7_pkey"
DETAIL: Key (id)=(3) already exists.

Delete constraint

To delete a constraint, you must know the name of the constraint. It is simple to delete a constraint if you know the name, but if you do not know the name, you need to find the system-generated name. You can find this information using \d table_name.

The general syntax is as follows:

ALTER TABLE table_name DROP CONSTRAINT some_name;