PostgreSQL constraints are used to specify data rules in a table.
If there is a data behavior that violates the constraint, the behavior is terminated by the constraint.
Constraints can be specified when creating a table (through the CREATE TABLE Statement, or specify it after the table is created (through the ALTER TABLE Statement).
Constraints ensure the accuracy and reliability of the data in the database.
Constraints can be at the column or table level. Column-level constraints apply only to columns, and table-level constraints are applied to the entire table.
The following constraints are commonly used in PostgreSQL.
NOT NULL Indicates that a column cannot store NULL values
UNIQUE Make sure that the values of a column are unique
The combination of PRIMARY Key:NOT NULL and UNIQUE. Ensuring that a column (or a combination of two columns with multiple columns) has a unique identity makes it easier and faster to find a specific record in the table. no, no, no.
FOREIGN Key: ensures referential integrity that the data in one table matches the values in another table.
CHECK: ensures that the values in the column meet the specified criteria.
EXCLUSION: an exclusive constraint that ensures that at least one operator comparison will return a false or null value if any two rows of specified columns or expressions are compared using the specified operator.
5.28.1. NOT NULL constraint
By default, columns can be saved as NULL Value. If you don’t want a column to have NULL Value, then you need to define this constraint on the column, specifying that it is not allowed on the column NULL Value.
NULL Unlike no data, it represents unknown data.
实例
The following example creates a new table called COMPANY1 with five fields added, of which three ID,NAME,AGE settings do not accept vacancy:
CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
5.28.2. UNIQUE constraint
UNIQUE Constraints can set the column to be unique to avoid duplicate values in the same column.
实例
The following example creates a new table called COMPANY3 , adding five fields, of which AGE Set to UNIQUE So you cannot add two records of 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 );
5.28.3. PRIMARY KEY
PRIMARY KEY is very important when designing a database.
The PRIMARY KEY, called the primary key, is the unique identification of each record in the data table.
There may be more than one column that sets UNIQUE, but only one column in a table can set PRIMARY KEY.
We can use the primary key to reference rows in the table, or we can create relationships between tables by setting the primary key to the foreign key of other tables.
The primary key is a combination of non-null and unique constraints.
A table can have only one primary key, it can consist of one or more fields, and when multiple fields are used as primary keys, they are called compound keys.
If a table defines a primary key on any field, no two records can have the same value on those fields.
实例
Let’s 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 );
5.28.4. FOREIGN KEY constraint
FOREIGN KEY is a foreign key constraint that specifies that values in a column (or set of columns) must match values that appear in a row of another table.
Usually the FOREIGN KEY in one table points to the UNIQUE KEY (the key of the unique constraint) in another table, which maintains referential integrity between two related tables.
实例
The following example creates a COMPANY6 table and adds five 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 add 3 fields EMP_ID It is the foreign key, refer to COMPANY6 Of ID :
CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY6(ID) );
5.28.5. CHECK constraint
The CHECK constraint ensures that all values in the column meet a condition that an input record is checked. If the condition value is false, the record violates the constraint and cannot be entered into the table.
实例
For example, the following example creates a new table, COMPANY5, with five columns added. Here, we add CHECK to 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) );
5.28.6. EXCLUSION constraint
The EXCLUSION constraint ensures that if any two rows are compared on the specified column or expression using the specified operator, at least one of the operator comparisons will return false or null.
实例
The following example creates a COMPANY7 table, adds five fields, and uses the EXCLUDE Restraint.
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 execute once for each database CREATE EXTENSION btree_gist Command, which installs the btree_gist Extension that defines the EXCLUDE Restraint.
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 ); -- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件 INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 ); -- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入
The first two items were successfully added to the COMPANY7 table, but the third one reported 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).
5.28.7. Delete constraint
To delete a constraint, you must know the constraint name. It is easy to delete the constraint by already knowing the name. If you do not know the name, you need to find the system-generated name, and use thed table name to find this information.
The general syntax is as follows:
ALTER TABLE table_name DROP CONSTRAINT some_name;