The constraints of PostgreSQL

Constraint type: Check constraint, non-null constraint, UNIQUE constraint, primary key, foreign key

1. CHECK constraints

Setting a value in a field must satisfy the condition of the constraint expression.

Example: Limiting the age of people in 0~120 , the statement is as follows:

CREATE TABLE person (Namevarchar (+), Age int. check (age >=0 and age<=120));

INSERT into person values (' name1 ', 120);

INSERT into person values (' name1 ', 121);

the execution results are as follows, the age field exceeds - error, prompted by " Person_age_check "restrictions.

Specifies the name of the constraint, which requires the use of the keyword " constraint ", as shown for example under

CREATE TABLE person (Namevarchar (+), age int. constraint age_chk check (age >=0 and age<=120);

A check constraint can refer to multiple columns, such as storing the normal and discounted prices of the goods, while the discounted price is below the usual price, as shown below

CREATE TABLE Products (

product_no Integer,

Name text,

Price Numeric check (Price > 0),

dazhe_price Numeric check (Dazhe_price > 0),

constraint dazhe_price_chk check (price >dazhe_price)

);

in the example above, for the price ( Price > 0 ) and price after discount ( dazhe_price > 0 is called a column constraint, and the constraints are tied to a specific column, but the third constraint ( Price >dazhe_price ) is independent of any one of the column definitions, called table constraints.

The results of the implementation are as follows:

Note: Oracle , the CHECK constraint can be disabled, but the PostgreSQL can not be disabled.

2. Non-null constraint

a non-null constraint only specifies that there are no null values in a column. A non-null constraint is equivalent to a check constraint (column_name is not null). Examples are as follows:

CREATE TABLE Products (

product_no Integer,

Name text,

Price numeric not null,

dazhe_price Numeric check (Dazhe_price > 0),

constraint dazhe_price_chk check (price >dazhe_price)

);

3. Unique constraint

The unique constraint guarantees that the data saved in a column or set of columns is a unique value, as shown in the following example:

CREATE TABLE Products (

product_no integer UNIQUE,

Name text,

Price Numeric

);

Define a UNIQUE constraint for a set of columns, as shown in the following example:

CREATE TABLE Products (

product_no Integer,

Name text,

Price Numeric,

UNIQUE(product_no, Price)

);

Specify a name for the unique constraint:

CREATE TABLE Products (

product_no integer CONSTRAINT pro_no_uniqueunique,

Name text,

Price Numeric

);

The constraints of PostgreSQL