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

Изображение corochoone

Автор оригинальной статьи: Брюс Момьян
Оригинальная статья находится по адресу: 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.

          test=> CREATE TABLE not_null_test (
        test(>                             col1 INTEGER, 
        test(>                             col2 INTEGER NOT NULL
        test(>                            );
        CREATE
        test=> INSERT INTO not_null_test
        test-> VALUES (1, NULL);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test (col1)
        test-> VALUES (1);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test VALUES (1, 1);
        INSERT 174368 1
        test=> UPDATE  not_null_test SET col2 = NULL;
        ERROR:  ExecReplace: Fail to add null value in not null attribute col2
 

Попытка вставить значение 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 (уникальность)

Ограничение UNIQUE предотвращает появление в колонке одинаковых значений. Это достигается созданием уникального индекса для этой колонки. Как показывает следующий пример, 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.

Данный пример показывает создание ограничения UNIQUE для нескольких колонок.  

        test=> CREATE TABLE uniquetest2 (
        test(>                           col1 INTEGER, 
        test(>                           col2 INTEGER, 
        test(>                           UNIQUE (col1, col2)
        test(>                          );
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest2_col1_key' for table 'uniquetest2'
 

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

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

Ограничение PRIMARY KEY, является комбинацией ограничений UNIQUE и NOT NULL. Накладывание этого ограничения на колонку означает, что её значения будут являться уникальными идентификаторами для каждой строки таблицы. Следующий пример показывает создание колонки с ограничением  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
 

Заметим, что индекс создаётся автоматически и колонка определяется с наложением ограничения NOT NULL.

Как и в случае с UNIQUE, ограничение PRIMARY KEY для нескольких колонок (так называемый составной ключ -- прим. пер.) должно быть определено в отдельной строке. В следующем примере ограничение первичного ключа накладывается на комбинацию колонок col1 и col2.  

        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
 

У таблицы не может быть более одной спецификации 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
 

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

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

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


        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
 

Это новая таблица customer, где задана опция ON UPDATE CASCADE, которая говорит, что если первичный ключ в таблице statename обновляется, то значение колонки customer.state также должно быть обновлено, согласно новому значению. Опция внешнего ключа ON DELETE SET NULL говорит, что если кто-то пытается удалить строку в таблице  statename, на которую ссылается другая таблица, то операция удаления должна установить внешний ключ в значение NULL.

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

NO ACTION
Операторы UPDATE и DELETE для первичного ключа запрещаются, если на первичный ключ ссылается какой-либо внешний ключ. Это действие по умолчанию.
CASCADE
Оператор UPDATE для первичного ключа обновляет все колонки внешнего ключа, которые ссылаются на этот первичный ключ. Выполнение оператора DELETE для первичного ключа приводит к удалению всех строк с внешними ключами, которые ссылаются на этот первичный ключ.
SET NULL
Выполнение операторов UPDATE и DELETE для первичного ключа приводит к установке значений внешнего ключа в NULL.
SET DEFAULT
Выполнение операторов UPDATE и DELETE для первичного ключа приводит к установке значений внешнего ключа в значение, указанное в DEFAULT.
Пример иллюстрирует использование действий CASCADE и NO ACTION.  

        test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
        CREATE
        test=> CREATE TABLE foreigntest (
        test(>                           col2 INTEGER REFERENCES primarytest
        test(>                           ON UPDATE CASCADE 
        test(>                           ON DELETE NO ACTION
        test(>                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> INSERT INTO primarytest values (1);
        INSERT 148835 1
        test=> INSERT INTO foreigntest values (1);
        INSERT 148836 1
        test=>
        test=> -- CASCADE UPDATE is performed
        test=>
        test=> UPDATE primarytest SET col = 2;
        UPDATE 1
        test=> SELECT * FROM foreigntest;
         col2 
        ------
            2
        (1 row) 
         
        test=> 
        test=> -- NO ACTION prevents deletion
        test=>
        test=> DELETE FROM primarytest;
        ERROR:  <unnamed> referential integrity violation - key in primarytest still referenced from foreigntest
        test=>
        test=> -- By deleting the foreign key first, the DELETE succeeds
        test=>
        test=> DELETE FROM foreigntest;
        DELETE 1
        test=> DELETE FROM primarytest;
        DELETE 1
 

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

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

Чтобы задать первичный ключ для нескольких колонок (составной ключ), при создании таблицы в операторе CREATE TABLE необходимо указать ограничение PRIMARY KEY отдельной строкой. Составной внешний ключ создаётся точно также. Используя таблицу primarytest2 из предыдущего примера, следующий пример показывает как создать составной внешний ключ.  


        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 во Внешнем Ключе

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

Использование выражения MATCH FULL при определении ограничения составного внешнего ключа, требует, чтобы все колонки, которые входят в ключ имели значение  NULL или чтобы ни одна из колонок, входящих в ключ не имела значение 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
 

Сперва, таблицы из предыдущего примера используются, чтобы показать, что по умолчанию разрешается устанавливать одну колонку составного внешнего ключа в  NULL. Затем, создаётся таблица matchtest с опцией ограничения внешнего ключа MATCH FULL. MATCH FULL разрешает всем значениям колонок, которые входят в ключ быть установленными в значение NULL, но запрещает установку только некоторых из них.

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

По умолчанию, ограничения внешнего ключа проверяются в конце каждого запроса INSERT, UPDATE и DELETE. Таким образом, если вы выполняете множественные изменения таблиц, ограничения внешнего ключа должны сохраняться целостными всё это время. Например, используя таблицы из предыдущего примера, если добавляется новый штат и затем новый покупатель в этом новом штате, то новый штат должен быть добавлен в таблицу statename перед добавлением покупателя в таблицу customer.

В некоторых случаях, невозможно сохранить ограничения внешнего ключа между запросами. Например, если две таблицы имеют внешние ключи одна для другой, то невозможно выполнить INSERT для одной таблицы без того, чтобы вставляемая строка уже существовала в другой. Решением такой проблемы служит использование опции внешнего ключа DEFERRABLE и выполнение оператора SET CONSTRAINTS, чтобы ограничения внешнего ключа проверялись только при окончании транзакции (выполнении commit). В этом случае, транзакции с несколькими запросами могут производить внутри транзакции изменения таблиц, которые защищены ограничениями внешнего ключа, до тех пор пока не произойдёт окончание транзакции. Следующий пример иллюстрирует данный случай; правильным способом выполнения этого запроса является выполнение сначала INSERT для primarytest, а затем INSERT в defertest.  


        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 (проверка)

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


        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.
Далее, пример пытается выполнить INSERT для строки, которая защищена всеми указанными ограничениями CHECK. Поскольку проверка CHECK закончилась неуспешно для ограничения friend_last_met, если проблемное значение будет исправлено, то добавление строки не будет допущено другими ограничениями. По умолчанию CHECK разрешает значения NULL.

Аннотация

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

Об авторе

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.

Комментарии

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

кое-какие замечания

я понимаю, что это переведенная статья, но в разделе Foreign Key - Изменение строки с Первичным Ключом указана не вся информация, я имею в виду отсутствие Restrict. Вот отрывок из хелпа:
Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.)

И еще: на этой странице у меня вся правая колонка сползла под статью.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2010