Constraints
Tags: opengauss
Published: 2023-03-06 09:57:58

Constraint clauses specify constraints that new or updated rows must satisfy for an INSERT or UPDATE operation to succeed. If there is any data behavior that violates the constraints, the behavior is terminated by the constraints.

Constraints can be specified when a table is created (by executing the CREATE TABLE statement) or after a table is created (by executing the ALTER TABLE statement).

Constraints can be column-level or table-level. Column-level constraints apply only to columns, and table-level constraints apply to the entire table.

The common constraints of openGauss are as follows:

· NOT NULL: specifies that a column cannot store **NULL** values.
· UNIQUE: ensures that the value of a column is unique.
· PRIMARY KEY: functions as the combination of NOT NULL and UNIQUE and ensures that a column (or the combination of two or more columns) has a unique identifier to help quickly locate a specific record in a table.
· FOREIGN KEY: ensures the referential integrity for data in one table to match values in another table.
· CHECK: ensures that values in a column meet specified conditions.

**NOT NULL**

If no constraint is specified during table creation, the default value is **NULL**, indicating that **NULL** values can be inserted into columns. If you do not want a column to be set to **NULL**, you need to define the **NOT NULL** constraint on the column to specify that **NULL** values are not allowed in the column. When you insert data, if the column contains **NULL**, an error is reported and the data fails to be inserted.

**NULL** does not mean that there is no data. It indicates unknown data.

For example, create the **staff** table that contains five columns. The **NAME** and **ID** columns cannot be set to **NULL**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ot7hsl9rldhpczfg5cv.png)

Insert data into the **staff** table. When a **NULL** value is inserted into the **ID** column, the database returns an error.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rbgd4hrodg1xr6166dp3.png)

**UNIQUE**

The UNIQUE constraint specifies that a group of one or more columns of a table can contain only unique values.

For the UNIQUE constraint, **NULL** is not considered equal.

For example, create the **staff1** table that contains five columns, where **AGE** is set to **UNIQUE**. Therefore, you cannot add two records with the same age.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qpskvr8fgkb310ad91ke.png)

Insert data into the **staff1** table. When two identical data records are inserted into the **AGE** column, the database returns an error.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/edpn1uar59g8jii2ozaw.png)

**PRIMARY KEY**

PRIMARY KEY is the unique identifier of each record in a data table. It specifies that a column or multiple columns in a table can contain only unique (non-duplicate) and non-**NULL** values.

PRIMARY KEY is the combination of NOT NULL and UNIQUE. Only one primary key can be specified for a table.

For example, create the **staff2** table where **ID** indicates the primary key.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nvvxxgutl1w3mt5t8h6g.png)

**FOREIGN KEY**

The FOREIGN KEY constraint specifies that the value of a column (or a group of columns) must match the value in a row of another table. Generally, the FOREIGN KEY constraint in one table points to the UNIQUE KEY constraint in another table. That is, the referential integrity between two related tables is maintained.

For example, create the **staff3** table that contains five columns.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x4dynyqi39qppoi6t098.png)

Create the **DEPARTMENT** table and add three columns. The **EMP_ID** column indicates the foreign key and it is similar to the **ID** column of the **staff3** table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ayvotu8jnaub1ahhz6sb.png)

**CHECK**
The CHECK constraint specifies an expression producing a Boolean result where the INSERT or UPDATE operation of new or updated rows can succeed only when the expression result is **TRUE** or **UNKNOWN**; otherwise, an error is thrown and the database is not altered.

A CHECK constraint specified as a column constraint should reference only the column's value, while an expression in a table constraint can reference multiple columns. **<>NULL** and **!=NULL** are invalid in an expression. Change them to IS **NOT NULL**.

For example, create the **staff4** table and add a CHECK constraint to the **SALARY** column to ensure that the inserted value is greater than **0**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z78h834023nse1yb9di1.png)

Insert data into the **staff4** table. When the inserted value of the **SALARY** column is not greater than 0, the database reports an error.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h9vc4xl2feputn4nqkkp.png)