Add Column Constraint PostgreSQL

Faruk Erdem February 5, 2023 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.

create table personnel( adres text  NULL)

1	create table personnel( adres text NULL)

NOT NULL:

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

create table personnel( TC_No integer  NOT NULL)

1	create table personnel( TC_No integer NOT NULL)

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.

create table Location( city UNIQUE)

1	create table Location( city UNIQUE)

CHECK:

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

CREATE TABLE personnel(birth_date date CHECK (birth_date>'1900-01-01')

1	CREATE TABLE personnel(birth_date date CHECK (birth_date>'1900-01-01')

PRIMARY KEY:

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

create table personnel( id serial  PRIMARY KEY)

1	create table personnel( id serial PRIMARY KEY)

REFERENCES:

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

CREATE TABLE public.personnel_birth_date
(
     id integer,
     birth_date date,
     p_id integer,
     CONSTRAINT personnelfk FOREIGN KEY (p_id)
         REFERENCES public.personnel (id) --MATCH SIMPLE
         ON UPDATE NO ACTION
         ON DELETE NO ACTION
         NOT VALID
);

CREATE TABLE public.personnel_birth_date

(

id integer,

birth_date date,

p_id integer,

CONSTRAINT personnelfk FOREIGN KEY (p_id)

REFERENCES public.personnel (id) --MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE NO ACTION

NOT VALID

);

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

EXCLUDE:

This parameter is used for comparisons between two lines.

CREATE TABLE personnel(
    ID INT PRIMARY KEY NOT NULL,
    name CHAR(50),
    age INT ,
    address CHAR(50),
    salary REAL,
    EXCLUDE USING gist
    (name WITH =,
    age WITH <>)
);

CREATE TABLE personnel(

ID INT PRIMARY KEY NOT NULL,

name CHAR(50),

age INT ,

address CHAR(50),

salary REAL,

EXCLUDE USING gist

(name WITH =,

age WITH <>)

);

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.

INSERT INTO personel VALUES(1, 'faruk', 29, 'Ankara', 1000.00 );

INSERT INTO personel VALUES(2, 'faruk', 29, 'Afyon', 2000.00 );

INSERT INTO personel VALUES(3, 'faruk', 50, 'Konya', 200.00 );

INSERT INTO personel VALUES(1, 'faruk', 29, 'Ankara', 1000.00 );

INSERT INTO personel VALUES(2, 'faruk', 29, 'Afyon', 2000.00 );

INSERT INTO personel VALUES(3, 'faruk', 50, 'Konya', 200.00 );

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.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux