❮ Postgresql Data Type Postgresql Where Clause ❯

PostgreSQL Constraints

PostgreSQL constraints are used to define rules for data in tables.

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

Constraints can be defined when creating a table (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-level or table-level. Column-level constraints apply only to a column, while table-level constraints apply to the entire table.

Here are some commonly used constraints in PostgreSQL.

NOT NULL: Indicates that a column cannot store NULL values.
UNIQUE: Ensures that the values in a column are 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 in easily and quickly finding a specific record in the table.
FOREIGN KEY: Ensures referential integrity by guaranteeing that the data in one table matches the values in another table.
CHECK: Ensures that the values in a column meet a specific condition.
EXCLUSION: An exclusion constraint ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of the comparisons will return false or null.

NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to have NULL values, you need to define this constraint on that column, specifying that NULL values are not allowed.

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

Example

The following example creates a new table called COMPANY1 with five fields, where ID, NAME, and AGE are set to not accept NULL values:

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

UNIQUE Constraint

The UNIQUE constraint ensures that the column is unique and avoids duplicate values in the same column.

Example

The following example creates a new table called COMPANY3 with five fields, where 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, the PRIMARY KEY is very important.

The PRIMARY KEY is known as the primary key and is the unique identifier for each record in the table.

There may be multiple columns with a UNIQUE constraint, but only one column in a table can be set as the PRIMARY KEY.

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

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

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

If a primary key is defined on any fields in a table, no two records can have the same values in those fields.

Example

The following example creates a table called COMPANY4, 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

The FOREIGN KEY constraint specifies that the values in a column (or a set of columns) must match the values appearing in some row of another table.

Typically, a FOREIGN KEY in one table points to a UNIQUE KEY in another table, maintaining referential integrity between the two related tables.

Example

The following example creates a table called COMPANY6 with five fields:

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

This is a Chinese to English translation. Here is the English translation of the text:

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

CHECK Constraint

A CHECK constraint ensures that all values in a column meet a specific condition, meaning that a record must be checked when entered. If the condition evaluates to false, the record violates the constraint and cannot be entered into the table.

Example

For instance, the following example creates a new table COMPANY5 with five columns. Here, we add a CHECK constraint 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

An EXCLUSION constraint ensures that if any two rows are compared using specified operators on specified columns or expressions, at least one of the operator comparisons will return false or null.

Example

The following example creates a COMPANY7 table with five columns and uses an EXCLUDE constraint:

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 but AGE is different, insertion is not allowed; otherwise, it is allowed
   AGE WITH <>)   -- The comparison result is that if the entire expression returns true, insertion is not allowed; otherwise, it is allowed
);

Here, USING gist is a type of index used for construction and execution.

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

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 );
-- This record has the same NAME and AGE as the first one, so it meets the insertion condition
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- This record has the same NAME as the above one but a different AGE, so it is not allowed to insert

The first two records were successfully added to the COMPANY7 table, but the third one will result in 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).

Dropping Constraints

To drop a constraint, you must know the constraint name. If you know the name, dropping the constraint is straightforward. If you do not know the name, you need to find the system-generated name using \d table_name.

The general syntax is as follows:

ALTER TABLE table_name DROP CONSTRAINT some_name;

❮ Postgresql Data Type Postgresql Where Clause ❯