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 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 the data in the database.
Constraints can be column or table level. Column-level constraints apply only to columns, and table-level constraints are applied to the entire table.
The following are the commonly used constraints in PostgreSQL.
- NOT NULL: Indicates that a column cannot store NULL values.
- UNIQUE: Make sure the values for a column are unique.
- PRIMARY Key: A combination of NOT NULL and UNIQUE. Ensuring that a column (or a combination of two columns and multiple columns) has a unique identifier helps make it easier and faster to find a particular record in the table. .
- FOREIGN Key: Ensures that the data in one table matches the referential integrity of values in another table.
- CHECK: Ensures that the values in the column meet the specified criteria.
- EXCLUSION : Exclusive constraint, guarantees that if any two rows of specified columns or expressions are compared using the specified operator, at least one of the operator comparisons will return false or null.
NOT NULL constraint
By default, columns can be saved as NULL values. If you do not want a column to have a NULL value, you need to define this constraint on that column, specifying that NULL values are not allowed on that column.
NULL is not the same as no data, it represents unknown data.
The following example creates a new table called COMPANY1, adding 5 fields, three of which are ID, NAME, and AGE settings are not accepted for vacancy:
CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
UNIQUE constraints can set columns to be unique, avoiding duplicate values in the same column.
The following example creates a new table called COMPANY3, adding 5 fields, where AGE is set to UNIQUE, so you can't 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 );
PRIMARY KEY is very important when designing a database.
PRIMARY KEY is called the primary key and is the unique identifier for each record in the data table.
There may be more than one column for setting UNIQUE, but only one column for a table can set the PRIMARY KEY.
We can use the primary key to refer to a row in a table, or we can create a relationship between tables by setting the primary key to a foreign key of another table.
The primary key is a combination of a non-null constraint and a unique constraint.
A table can only have one primary key. It can consist of one or more fields. When multiple fields are used as primary keys, they are called composite keys.
If a table defines a primary key on any field, then no two records on these fields have the same value.
Here we create a COMAPNY4 table with the 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 is a foreign key constraint where the value in the specified column (or set of columns) must match the value that appears in a row in another table.
The FOREIGN KEY in one table usually points to the UNIQUE KEY in another table (the only constraint key), which maintains referential integrity between the two related tables.
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, refer to the ID of COMPANY6:
CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY6(ID) );
The CHECK constraint guarantees that all values in the column satisfy a condition, that is, a record is entered for inspection. If the condition value is false, the record violates the constraint and cannot be entered into the table.
For example, the following example builds 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) );
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.
Here, USING gist is a type of index used to build and execute....
CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT, AGE INT , ADDRESS CHAR(50), SALARY REAL, EXCLUDE USING gist (NAME WITH =, -- If satisfied NAME the same，AGE Not the same, allowing insertion，Otherwise insert is not allowed AGE WITH <>) -- The result of the comparison is if the entire table side returns true，Insert is not allowed，Otherwise allowed );
Here, USING gist is a type of index used to build and execute.