Site icon Database Tutorials

Add Column Constraint PostgreSQL

In the table, we can use column restrictions on how to keep the data in general, whether to enter a null value in the relevant column in the table, what to do if an empty value is entered, and in cases where we do not want the same data to be entered more than once.

NULL:

It allows to enter a null value in the relevant column. If no constraint is entered while creating a column, default also takes the null parameter.

NOT NULL:

It is not allowed to enter a null value in the relevant column.

UNIQUE:

As the name suggests, the column also helps us to keep unique values, so you cannot enter the same value twice in the column with UNIQUE added, but it should be noted here that the NULL value may repeat more than once. This is because it sees NULL values as unique.

CHECK:

While adding or updating data, it checks the data according to the value you write after the CHECK parameter.

PRIMARY KEY:

It is a combination of NOT NULL and UNIQUE constraints. Clustered index is created automatically in columns with primary key added.

REFERENCES:

Restricts the data type of a different column in a different table.

I recommend you to make examples yourself using the above features.

EXCLUDE:

This parameter is used for comparisons between two lines.

Since we have entered the condition of being the same age in the table above, an error will return when trying to insert a different age belonging to the same name.

When we want to add the last insert, the error will return as follows.

ERROR: ERROR: mismatched record violates “personnel_name_age_excl” exclusion restriction DETAIL: key (name, “age”)=(faruk , 50) does not match existing (name, “age”)=(faruk , 29).

NOTE: You must install the btree_gist extension before using the exclude command.

Exit mobile version