Ограничения в PostgreSQL

Ограничения в PostgreSQL, или constraints, — это правила, которые защищают данные в таблицах от некорректных значений и нарушений целостности. С их помощью можно запретить NULL, обеспечить уникальность, задать первичный и внешний ключ, проверить условие или ограничить пересечение значений.

Ограничения бывают на уровне столбца и на уровне таблицы. Они помогают базе данных автоматически контролировать качество данных, связи между таблицами и бизнес-правила еще на этапе вставки или изменения строк. В статье разберем основные ограничения PostgreSQL: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK и EXCLUDE, а также покажем примеры их использования.

Что внутри:

что такое ограничения в PostgreSQL;
ограничения на уровне столбца и таблицы;
NOT NULL и запрет пустых значений;
UNIQUE и уникальность значений;
PRIMARY KEY как идентификатор строки;
FOREIGN KEY и ссылочная целостность;
CHECK и проверка условий;
EXCLUDE и исключающие ограничения;
примеры CREATE TABLE с constraints;
зачем ограничения нужны для надежности данных.

Ограничения – это правила для данных, содержащихся в столбцах таблицы. Они используются для того, чтобы не допустить добавление недействительной информации в базу данных. Это позволяет обеспечить надежность и достоверность данных, внесенных в базу данных.

Ограничения могут быть как на уровне столбцов, так и на уровне таблицы. Ограничения на уровне столбцов касаются только одного столбца, в то время как ограничения на уровне таблицы применимы ко всей таблице. Определение типа данных для столбца само по себе является ограничением. Например, столбец под названием ДАТА позволяет вводить только даты.

Самые распространенные ограничения, доступные в PostgreSQL:

NOT NULL – столбец не может содержать нулевые значения (NULL);
UNIQUE – все значения в столбце должны быть разные;
PRIMARY Key − идентифицирует каждую строку/запись в таблице базы данных;
FOREIGN Key − ограничивает данные на основе столбцов в других таблицах;
CHECK – все значения в столбце должны удовлетворять определенным условиям;
EXCLUSION − ограничение EXCLUDE гарантирует, что при сравнении любых двух строк в указанном столбце (столбцах) или выражении (выражениях) с использованием указанного оператора (операторов) не все эти сравнения вернут значение TRUE.

Ограничение NOT NULL

По умолчанию столбец может содержать значения NULL. Если для Вас это неприемлемо, тогда Вы можете установить для определенного столбца ограничение, благодаря которому NULL невозможно будет добавить в данный столбец. Ограничение NOT NULL всегда записывается, как ограничение на уровне столбца.

NULL – это не то же самое, что отсутствие данных; скорее, речь идет о неизвестных данных.

Пример

Например, команда, приведенная ниже, создаст новую таблицу под названием COMPANY1 и добавит пять столбцов, три из которых, ID, имя и возраст не смогут принять значения NULL:

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

Ограничение UNIQUE

Ограничение UNIQUE помогает избежать повторения значений в определенном столбце. Например, возможно, Вы не хотите, чтобы в таблице COMPANY два или более человек имели один и тот же возраст.

Пример

Следующая команда PostgreSQL создаст новую таблицу COMPANY3 и добавить пять столбцов. В данном случае на столбец AGE наложено ограничение UNIQUE, таким образом, Вы не сможете добавить двух человек одного и того же возраста:

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

Ограничение PRIMARY KEY идентифицирует каждую запись в таблице базы данных. УНИКАЛЬНЫХ столбцов может быть больше, но первичный ключ в таблице - всегда только один. Первичные ключи важны при проектировании таблиц базы данных, это уникальные идентификаторы.

Мы используем их для ссылки на строки таблицы. Первичные ключи становятся внешними ключами в других таблицах при создании отношений между таблицами.

Первичный ключ — это поле в таблице, которое однозначно идентифицирует каждую строку/запись в таблице базы данных. Первичные ключи должны содержать уникальные значения. Столбец первичного ключа не может иметь значений NULL.

Таблица может иметь только один первичный ключ, который может состоять из одного или нескольких полей. Когда несколько полей используются в качестве первичного ключа, они называются составным ключом.

Если в таблице есть первичный ключ, определенный для какого-либо поля (полей), то у Вас не может быть двух записей с одинаковым значением этого поля (полей).

Пример

Выше Вы уже видели несколько пример создания новых таблиц, в данном случае COMРАNY4 содержит столбец ID в качестве первичного ключа:

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

Ограничение FOREIGN KEY

Ограничение внешнего ключа указывает, что значения в столбце (или группе столбцов) должны совпадать со значениями, отображаемыми в определенной строке другой таблицы. Это обеспечивает ссылочную целостность между двумя связанными таблицами. Внешними ключами они называются, потому что ограничения являются внешними; то есть вне таблицы. Иногда их называют ссылочными ключами.

Пример

Создадим таблицу COMPANY6 и добавим пять столбцов:

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

Создадим еще оду таблицу DEPARTMENT1, куда добавим три столбца. Столбец EMP_ID – внешний ключ, который ссылается на поле ID таблицы COMPANY6.

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

Ограничение CHECK

Ограничение CHECK включает условие для проверки вводимого значения. Если условие оценивается как ложное, запись нарушает ограничение и не добавляется в таблицу.

Пример

Создадим новую таблицу COMPANY5 и добавим пять столбцов. Добавим ограничение CHECK для столбца SALARY (ЗАРПЛАТА), таким образом, Вы не сможете добавить зарплату равную 0.

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

Данное ограничение гарантирует, что при сравнении любых двух строк в указанных столбцах или выражениях с использованием указанных операторов хотя бы одно из этих сравнений вернет значение false или null.

Пример

Создадим таблицу COMPANY7 и добавим пять столбцов. Добавим ограничение EXCLUDE:

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

В данном случае USING gist – тип индекса, используемый для построения и использования для исполнения.

Вам необходимо выполнить команду CREATE EXTENSION btree_gist, один раз для базы данных. Это установит расширение btree_gist, которое определит ограничения исключения для простых скалярных типов данных.

Поскольку мы установили, что возраст должен быть одинаковым, давайте проверим это, вставив записи в таблицу:

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

Для первых двух команд INSERT значения добавлены в таблицу COMPANY7. Для третьей команды INSERT выводится следующая ошибка:

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).

Как сбросить ограничения

Для того чтобы убрать ограничение, необходимо знать его название. Если название известно, ограничение снять легко. В противном случае Вам нужно узнать сгенерированное системой имя. В этом случае psql команда \d table name может быть полезной. Базовый синтаксис выглядит следующим образом:

ALTER TABLE table_name DROP CONSTRAINT some_name;

FAQ

Вопрос: Что такое constraint в PostgreSQL?

Ответ: Constraint — это ограничение или правило для данных в таблице. Оно проверяется базой данных при вставке или изменении строк и помогает не допустить некорректные значения.

Вопрос: Чем NOT NULL отличается от CHECK?

Ответ: NOT NULL запрещает хранить NULL в столбце. CHECK позволяет задать произвольное логическое условие, которому должны соответствовать значения строки или столбца.

Вопрос: Чем UNIQUE отличается от PRIMARY KEY?

Ответ: UNIQUE требует уникальности значений в столбце или наборе столбцов. PRIMARY KEY также обеспечивает уникальность, но дополнительно идентифицирует строку таблицы и не допускает NULL.

Вопрос: Зачем нужен FOREIGN KEY?

Ответ: FOREIGN KEY связывает данные одной таблицы с данными другой таблицы и помогает поддерживать ссылочную целостность. Например, заказ не сможет ссылаться на несуществующего клиента.

Вопрос: Что такое EXCLUDE constraint?

Ответ: EXCLUDE constraint запрещает одновременное выполнение заданного сравнения для двух строк. Его часто используют для предотвращения пересекающихся диапазонов, например интервалов времени.

← Предыдущая статья

PostgreSQL – ключевое слово DISTINCT

Следующая статья →

PostgreSQL - операторы JOINS