PostgreSQL Ограничения
Ограничения PostgreSQL используются для задания правил для данных в таблице.
Если какое-либо действие нарушает ограничения, оно будет прервано ограничением.
Ограничения можно указать при создании таблицы (с помощью оператора CREATE TABLE) или после создания таблицы (с помощью оператора ALTER TABLE).
Ограничения обеспечивают точность и надёжность данных в базе данных.
Ограничения могут быть уровня столбца или уровня таблицы. Ограничения уровня столбца применяются только к столбцу, тогда как ограничения уровня таблицы применяются ко всей таблице.
Ниже приведены часто используемые ограничения в PostgreSQL.
- NOT NULL: Указывает, что столбец не может хранить значения NULL.
- UNIQUE: Гарантирует, что все значения в столбце уникальны.
- PRIMARY Key: Комбинация NOT NULL и UNIQUE. Гарантирует, что столбец (или комбинация из двух или более столбцов) имеет уникальный идентификатор, что помогает быстрее и проще находить конкретную запись в таблице.
- FOREIGN Key: Обеспечивает ссылочную целостность, гарантируя, что данные в одной таблице соответствуют значениям в другой таблице.
- CHECK: Обеспечивает, что значения в столбце удовлетворяют заданному условию.
- EXCLUSION: Ограничение исключения гарантирует, что если любые две строки сравниваются по указанным столбцам или выражениям с использованием указанных операторов, по крайней мере одно из сравнений оператора вернёт false или null.
Ограничение NOT NULL
По умолчанию столбец может содержать значения NULL. Если вы не хотите, чтобы столбец содержал значения NULL, необходимо определить это ограничение на данном столбце, указав, что значения NULL не допускаются в этом столбце.
NULL — это не то же самое, что отсутствие данных; он представляет неизвестные данные.
Пример
Следующий пример создаёт новую таблицу с именем COMPANY1 с 5 полями, три из которых — ID, NAME и AGE — настроены на запрет значений 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 может сделать столбец уникальным, предотвращая дублирование значений в одном и том же столбце.
Пример
Следующий пример создаёт новую таблицу с именем COMPANY3 с 5 полями. Поле 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 очень важен.
PRIMARY KEY, также называемый первичным ключом, — это уникальный идентификатор каждой записи в таблице данных.
Может быть несколько столбцов, для которых задано UNIQUE, но только один столбец в таблице может быть задан как PRIMARY KEY.
Мы можем использовать первичный ключ для ссылки на строки в таблице, а также создавать связи между таблицами, задав первичный ключ как внешний ключ в других таблицах.
Первичный ключ — это комбинация ограничения NOT NULL и ограничения UNIQUE.
Таблица может иметь только один первичный ключ, который может состоять из одного или нескольких полей. Когда в качестве первичного ключа используется несколько полей, они называются составным ключом.
Если таблица определяет первичный ключ по любому полю или полям, то никакие две записи не могут иметь одинаковые значения в этих полях.
Пример
Далее мы создаём таблицу COMAPNY4, где 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
FOREIGN KEY, также известный как ограничение внешнего ключа, указывает, что значения в столбце (или наборе столбцов) должны совпадать со значениями, встречающимися в некоторой строке другой таблицы.
Обычно FOREIGN KEY в одной таблице указывает на UNIQUE KEY (ключ с ограничением уникальности) в другой таблице, поддерживая таким образом ссылочную целостность между двумя связанными таблицами.
Пример
В следующем примере создаётся таблица COMPANY6 и добавляются 5 полей:
CREATE TABLE COMPANY6( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
В следующем примере создаётся таблица DEPARTMENT1 и добавляются 3 поля. 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 гарантирует, что все значения в столбце удовлетворяют определённому условию, то есть оно проверяет каждую вводимую запись. Если условие принимает значение false, запись нарушает ограничение и не может быть добавлена в таблицу.
Пример
Например, в следующем примере создаётся новая таблица COMPANY5 с пятью столбцами. Здесь мы добавляем ограничение CHECK на столбец SALARY, чтобы зарплата не могла быть равна нулю:
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
Ограничение EXCLUSION гарантирует, что если любые две строки сравниваются по указанным столбцам или выражениям с использованием указанных операторов, по крайней мере одно из сравнений оператора вернёт false или null.
Пример
В следующем примере создаётся таблица COMPANY7 с 5 полями и используется ограничение EXCLUDE.
CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT, AGE INT , ADDRESS CHAR(50), SALARY REAL, EXCLUDE USING gist (NAME WITH =, -- 如果满足 NAME 相同,AGE 不相同则不允许插入,否则允许插入 AGE WITH <>) -- 其比较的结果是如果整个表达式返回 true,则不允许插入,否则允许 );
Здесь USING gist — это тип индекса, используемый для построения и выполнения.
Вам нужно один раз выполнить команду CREATE EXTENSION btree_gist для каждой базы данных. Это установит расширение btree_gist, которое определяет ограничения EXCLUDE для обычных скалярных типов данных.
Поскольку мы задали требование, что возрасты должны быть равны, давайте проверим это, вставив записи в таблицу:
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 ); -- 此条数据的 NAME 与第一条相同,且 AGE 与第一条也相同,故满足插入条件 INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 ); -- 此数据与上面数据的 NAME 相同,但 AGE 不相同,故不允许插入
Первые две успешно добавляются в таблицу COMPANY7, но третья вызовет ошибку:
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).
Удаление ограничений
Чтобы удалить ограничение, необходимо знать его имя. Если вы уже знаете имя, удалить ограничение просто. Если вы не знаете имя, нужно найти сгенерированное системой имя с помощью\d table_nameЭту информацию можно найти.
Общий синтаксис выглядит следующим образом:
ALTER TABLE table_name DROP CONSTRAINT some_name;Другие расширения