5.28. PostgreSQL constraint

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;