Автор оригинальной статьи: Брюс Момьян
Оригинальная статья находится по адресу: http://momjian.postgresql.org/main/writings/pgsql/chapters/constraints/

Ограничения целостности в PostgreSQL

POSTGRESQL является объектно-реляционной СУБД, активно разрабатываемой Internet сообществом. Вы можете узнать об этом больше, посетив http://www.postgresql.org.

Ограничения целостности, вводят некоторые ограничения для данных пользователя, тем самым помогая предотвратить ввод неправильных данных в базу. Когда мы назначаем для какой-либо колонки в таблице определённый тип данных, мы вводим ограничение на эту колонку. Например, колонка типа DATE ограничивает ввод данных, позволяя вводить только правильные значения дат.

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

NOT NULL(не NULL)

Ограничение NOT NULL не допускает появление значений NULL в колонке. Следующий пример показывает создание таблицы с ограничением NOT NULL.

Попытка вставить значение NULL, или выполнение оператора INSERT, который устанавливает col2 в NULL приведёт к ошибке выполнения оператора INSERT. Как показано в нижеследующем примере, попытка выполнения оператора UPDATE с использованием значения NULL также приведёт к ошибке.

Данный пример добавляет значение DEFAULT для колонки col2.

        test=> CREATE TABLE not_null_with_default_test (
        test(>                                          col1 INTEGER,
        test(>                                          col2 INTEGER NOT NULL DEFAULT 5
        test(>                                         );
        CREATE
        test=> INSERT INTO not_null_with_default_test (col1) 
        test-> VALUES (1);
        INSERT 148520 1
        test=> SELECT * 
        test-> FROM not_null_with_default_test;
         col1 | col2 
        ------+------
            1 |    5
        (1 row)  

Таким образом разрешается выполнение операторов INSERT, в которых не задано значение для колонки col2.

UNIQUE (уникальность)

        test=> CREATE TABLE uniquetest (col1 INTEGER UNIQUE);
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest_col1_key' for table 'uniquetest'
        CREATE
        test=> \d uniquetest
               Table "uniquetest"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
        Index: uniquetest_col1_key 
         
        test=> INSERT INTO uniquetest VALUES (1);
        INSERT 148620 1
        test=> INSERT INTO uniquetest VALUES (1);
        ERROR:  Cannot insert a duplicate key into unique index uniquetest_col1_key
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT 148622 1
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT  

Сообщение команды CREATE TABLE содержит имя созданного уникального индекса. Пример также показывает, что в колонку с ограничением UNIQUE может быть вставлено несколько значений  NULL.

Если ограничение UNIQUE накладывается более чем на одну колонку, то UNIQUE нельзя использовать при определении колонки. Вместо этого, вы должны использовать отдельную строку с определением UNIQUE и указанием колонок, на которые накладывается ограничение. Такой способ создаёт табличное ограничение UNIQUE.

PRIMARY KEY (первичный ключ)

        test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
        CREATE
        test=> \d primarytest 
              Table "primarytest"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col       | integer | not null
        Index: primarytest_pkey  

У таблицы не может быть более одной спецификации PRIMARY KEY. Первичные ключи имеют специальный смысл, когда используются внешние ключи, описываемые в следующей секции.

Foreign Key/REFERENCES (внешние ключи/ссылки)

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

Например, пусть некая таблица содержит адреса покупателей и частью каждого адреса является двухсимвольный код одного из штатов США. Если есть ещё одна таблица, которая содержит все доступные коды штатов, то для того, чтобы предотвратить ввод пользователем неправильного кода, может быть создано ограничение внешнего ключа.

Пример показывает создание отношения первичного ключа/внешнего ключа.  

        test=>  CREATE TABLE statename (code CHAR(2) PRIMARY KEY,
        test(>                          name  CHAR(30) 
        test(> );
        CREATE
        test=> INSERT INTO statename VALUES ('AL', 'Alabama');
        INSERT 18934 1
        ... 
         
        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE  

Ограничения внешнего ключа, создаются используя слово REFERENCES ссылаясь на первичный ключ в другой таблице. Внешние ключи связывают таблицы вместе и предотвращают вставку или обновление неправильных данных.

Этот пример показывает как внешние ключи ограничивают значения в колонке.  

        test=> INSERT INTO customer (state) 
        test-> VALUES ('AL');
        INSERT 148732 1
        test=> INSERT INTO customer (state)
        test-> VALUES ('XX');
        ERROR:  <unnamed> referential integrity violation - key referenced from customer not found in statename  
Здесь значение AL является первичным ключём в таблице statename, так что оператор вставки INSERT принимается. А значение XX не является первичным ключём в таблице statename, так что оператор INSERT отвергается из-за ограничения внешнего ключа.

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2),
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
        test=> CREATE TABLE employee (
        test(>                        employee_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        hire_date   DATE
        test(> );
        CREATE
        test=> CREATE TABLE part (
        test(>                    part_id     INTEGER PRIMARY KEY,
        test(>                    name        CHAR(30),
        test(>                    cost        NUMERIC(8,2),
        test(>                    weight      FLOAT
        test(> );
        CREATE
        test=> CREATE TABLE salesorder (
        test(>                          order_id      INTEGER,
        test(>                          customer_id   INTEGER REFERENCES customer,
        test(>                          employee_id   INTEGER REFERENCES employee,
        test(>                          part_id       INTEGER REFERENCES part,
        test(>                          order_date    DATE,
        test(>                          ship_date     DATE, 
        test(>                          payment       NUMERIC(8,2)
        test(> );
        CREATE  

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

Изменение строки с Первичным Ключом

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename 
        test(>                                            ON UPDATE CASCADE 
        test(>                                            ON DELETE SET NULL,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE  

В опциях ON UPDATE и ON DELETE можно указывать следующие действия:

NO ACTION

Операторы UPDATE и DELETE для первичного ключа запрещаются, если на первичный ключ ссылается какой-либо внешний ключ. Это действие по умолчанию.

CASCADE

Оператор UPDATE для первичного ключа обновляет все колонки внешнего ключа, которые ссылаются на этот первичный ключ. Выполнение оператора DELETE для первичного ключа приводит к удалению всех строк с внешними ключами, которые ссылаются на этот первичный ключ.

SET NULL

Выполнение операторов UPDATE и DELETE для первичного ключа приводит к установке значений внешнего ключа в NULL.

SET DEFAULT

Выполнение операторов UPDATE и DELETE для первичного ключа приводит к установке значений внешнего ключа в значение, указанное в DEFAULT.

Сперва, пересоздаётся таблица primarytest, которая была использована в предыдущем примере. Затем, создаётся таблица foreigntest с использованием опций ON UPDATE CASCADE и ON DELETE NO ACTION. Опция NO ACTION включена по умолчанию, так что ON DELETE NO ACTION не требуется. Далее, в каждую из таблиц вставляется одна строка, и выполнение оператора UPDATE для таблицы primarytest приводит к каскадному выполнению UPDATE для таблицы foreigntest. Строка в таблице primarytest не может быть удалена до тех пор пока не удалён внешний ключ. Действия, выполняемые по ограничению внешнего ключа предлагают вам большую гибкость в управлении тем, как изменение первичного ключа будет влиять на строки внешнего ключа.

Первичные ключи для нескольких колонок

        test=> CREATE TABLE primarytest2 (
        test(>                            col1 INTEGER, 
        test(>                            col2 INTEGER, 
        test(>                            PRIMARY KEY(col1, col2)
        test(>                           );
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest2_pkey' for table 'primarytest2'
        CREATE
        test=> CREATE TABLE foreigntest2 (col3 INTEGER, 
        test(>                            col4 INTEGER,
        test(>                            FOREIGN KEY (col3, col4) REFERENCES primarytest2
        test->                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE  

При создании ограничения составного внешнего ключа необходимо использовать FOREIGN KEY (col, ...).

Управление значениями NULL во Внешнем Ключе

        test=> INSERT INTO primarytest2 
        test-> VALUES (1,2);
        INSERT 148816 1
        test=> INSERT INTO foreigntest2
        test-> VALUES (1,2);
        INSERT 148817 1
        test=> UPDATE foreigntest2
        test-> SET col4 = NULL;
        UPDATE 1
        test=> CREATE TABLE matchtest (
        test(>                    col3 INTEGER,
        test(>                    col4 INTEGER,
        test(>                    FOREIGN KEY (col3, col4) REFERENCES primarytest2 
        test(>                                             MATCH FULL
        test(>                        );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> UPDATE matchtest 
        test-> SET col3 = NULL, col4 = NULL;
        UPDATE 1
        test=> UPDATE matchtest
        test-> SET col4 = NULL;
        ERROR:  <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values  

Частота проверки Первичного Ключа

        test=> CREATE TABLE defertest( 
        test(>                        col2 INTEGER REFERENCES primarytest 
        test(>                                     DEFERRABLE
        test(> );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> BEGIN;
        BEGIN
        test=> -- INSERT is attempted in non-DEFERRABLE mode
        test=>
        test=> INSERT INTO defertest VALUES (5);
        ERROR:  <unnamed> referential integrity violation - key referenced from defertest not found in primarytest
        test=> COMMIT;
        COMMIT
        test=> BEGIN;
        BEGIN
        test=> -- all foreign key constraints are set to DEFERRED
        test=>
        test=> SET CONSTRAINTS ALL DEFERRED;
        SET CONSTRAINTS
        test=> INSERT INTO defertest VALUES (5);
        INSERT 148946 1
        test=> INSERT INTO primarytest VALUES (5);
        INSERT 148947 1
        test=> COMMIT;
        COMMIT  

В сложных ситуациях, такое изменение порядка бывает невозможно, так что в этих случаях, для отсроченных ограничений внешнего ключа должны использоваться  DEFERRABLE и SET CONSTRAINTS. Внешний ключ можно также сконфигурировать как INITIALLY DEFERRED, что по умолчанию установит проверку внешнего ключа только при окончании транзакции.

Если захотите, вы можете давать ограничениям имена. Имена ограничений будут появляться в сообщениях о защите по ограничению и могут быть использованы в операторе SET CONSTRAINTS. Подробности смотрите на страницах руководства по операторам  CREATE_TABLE и SET.

CHECK (проверка)

        test=> CREATE TABLE friend (
        test(>              firstname CHAR(15),
        test(>              lastname  CHAR(20),
        test(>              city      CHAR(15),
        test(>              state     CHAR(2)      CHECK (length(trim(state)) = 2),
        test(>              age       INTEGER      CHECK (age >= 0),
        test(>              gender    CHAR(1)      CHECK (gender IN ('M','F')),
        test(>              last_met  DATE         CHECK (last_met BETWEEN '1950-01-01'
        test(>                                            AND CURRENT_DATE),
        test(>              CHECK (upper(trim(firstname)) != 'ED' OR
        test(>                     upper(trim(lastname)) != 'RIVERS')
        test(> );
        CREATE
        test=> INSERT INTO friend
        test-> VALUES ('Ed', 'Rivers', 'Wibbleville', 'J', -35, 'S', '1931-09-23');
        ERROR:  ExecAppend: rejected due to CHECK constraint friend_last_met  

В данном примере есть несколько проверок CHECK:

state

Ограничивает колонку в длину двумя символами. CHAR() производит заполнение поля пробелами, так что значение state, перед тем как вызывать функции length() должно быть обработано функцией trim() - убирающей пробелы в конце.

age

Колонка ограничивается только положительными значениями.

gender

Колонка может содержать только значения M или F.

last_met

Колонка может включать только даты между January 1, 1950 и текущей датой.

table

Таблица допускает только строки, где значение firstname не ED или значение lastname не RIVERS. Такой эффект предотвращает ввод в таблицу значений Ed Rivers. Это имя будет отвергнуто, даже если оно написано только большими или только маленькими буквами и даже в смешанном регистре. Данное ограничение должно быть реализовано как ограничение CHECK уровня таблицы. Сравнение значения firstname с ED на уровне колонки приведёт к невозможности вводить всех людей с именем ED, что нам не нужно. Вместо этого, нам нужна определённая комбинация значений firstname и lastname.

Аннотация

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

Об авторе

Bruce Momjian является вице-президентом Database Development at Great Bridge, LLC, http://www.greatbridge.com и автором книги PostgreSQL: Introduction and Concepts, ©2001, Addison-Wesley, ISBN 0-201-70331-9. Данная статья взята из этой книги. Книга размещена на web-сайте http://www.postgresql.org/docs/awbook.html.