Ограничения целостности. Представления данных

Выбор таблицы-адресата для внешнего ключа

Столбцам внешнего ключа не запрещено ссылаться на столбцы своей же таблицы:

ALTER TABLE emp 
 ADD CONSTRAINT valid_manager
  FOREIGN KEY ( mgr ) REFERENCES emp ( empno )
;

В стандарте SQL такой внешний ключ называется рекурсивным.

Равным образом внешнему ключу разрешено ссылаться на столбцы таблицы из другой схемы. Только в этом случае потребуется иметь на таблицу из другой схемы привилегию REFERENCES:

CONNECT scott/tiger
-- соединились с СУБД как SCOTT
GRANT REFERENCES ON dept TO yard;
-- выдали право ссылаться внешним ключом на поля DEPT из схемы YARD
CONNECT yard/pass
-- соединились с СУБД как YARD
CREATE TABLE emp AS SELECT * FROM scott.emp;
-- создали таблицу EMP по образу одноименной в схеме SCOTT
ALTER TABLE emp 
   ADD 
FOREIGN KEY ( deptno ) REFERENCES scott.dept ( deptno )
;
-- установили ссылку на таблицу из другой схемы

Обратите внимание, что привилегии на SELECT к таблице-адресату в случае нахождения последней в иной схеме не требуется.

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

Поведение СУБД при попытке удалить "родительскую" запись

Обычное ограничение типа "внешний ключ" запрещает СУБД удалять родительскую запись, если на нее существуют в данный момент ссылки:

DELETE FROM dept WHERE deptno = 10;

Однако можно смоделировать и иную реакцию СУБД, разрешив-таки удаление родительской записи.

Указание ON DELETE CASCADE в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению подчиненных записей:

CREATE TABLE x ( a NUMBER PRIMARY KEY );
CREATE TABLE y ( b NUMBER PRIMARY KEY, 
                 c NUMBER REFERENCES x ( a ) ON DELETE CASCADE );
INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
DELETE FROM x;
SELECT * FROM y;

Обе таблицы пусты.

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

CREATE TABLE z ( d NUMBER PRIMARY KEY, 
                 e NUMBER REFERENCES y ( b ) ON DELETE CASCADE );
INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
INSERT INTO z VALUES ( 3, 2 );
DELETE FROM x;
SELECT * FROM z;

Автоматическим удалением по цепочке следует пользоваться с осторожностью.

Указание ON DELETE SET NULL в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению значений в полях-ссылках подчиненных записей:

CREATE TABLE w ( f NUMBER REFERENCES z ( d ) ON DELETE SET NULL );
INSERT INTO z VALUES ( 3, NULL );
INSERT INTO w VALUES ( 3 );
DELETE FROM z;
SELECT * FROM w;

Строка в таблице Z пропала, а в таблице W осталась (проверьте!).

Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа "внешний ключ" и не трогая значений подчиненных записей:

INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
DROP TABLE x CASCADE CONSTRAINTS;
SELECT * FROM y;
DROP TABLE y CASCADE CONSTRAINTS;
DROP TABLE z CASCADE CONSTRAINTS;
DROP TABLE w CASCADE CONSTRAINTS;

Стандарт SQL дает право задавать аналогичное поведение СУБД при попытках изменить родительскую запись, используя для этого формулировку ON UPDATE CASCADE. Oracle такой возможности не дает. Мнения специалистов по поводу целесообразности подобной формулировки разделились на противоположные.

Дополнительное условие для занесения значений в поля строки

Этот вид ограничения записывается с использованием ключевого слова CHECK. Он позволяет сформулировать в форме условного выражения дополнительные проверки на заносимые в поля строки значения. Если условное выражение окажется ложным, СУБД отвергнет изменение значений (реляционная теория предпочитает соблюдать истинность условия, а не отсутствие нарушения, что не одно и то же).

Пример:

ALTER TABLE dept_copy ADD CHECK ( category BETWEEN 'A' AND 'G' );
Другие примеры дополнительной проверки в описании столбца:
...
, zip   CHAR ( 6 ) CHECK ( REGEXP_LIKE ( zip, '[[:digit:]]{6}' ) ) )
, ename VARCHAR2 ( 30 ) CHECK ( ename = INITCAP ( ename ) )
...

Пример указания условных проверок при создании таблицы:

CREATE TABLE emp1 
(
  empno   NUMBER
, job     VARCHAR2 ( 9 ) DEFAULT 'SALESMAN'
, sal     NUMBER   ( 10, 2 )
, comm    NUMBER   ( 9, 0 ) 
,
  CONSTRAINT pk_emp1 PRIMARY KEY ( empno )
, CONSTRAINT ck_sal  CHECK ( sal >= 500 )
, CONSTRAINT check_whole_earning CHECK ( sal + comm < 7000 )
, CONSTRAINT sale_commission CHECK ( job = 'SALESMAN' OR comm IS NULL )
); 

Упражнение. Попробуйте выполнить:

INSERT INTO emp1 ( empno, sal, comm ) VALUES ( 1, 500, 1000 );
INSERT INTO emp1 ( empno, sal, comm ) VALUES ( 2, 8000, 1000 );
INSERT INTO emp1 ( empno, sal )       VALUES ( 3, 8000 );

Как нужно изменить определение таблицы, чтобы исправить ситуацию и сообщить СУБД интуитивное понимание правила SAL + COMM < 7000? Решите аналогичную проблему для ограничения SALE_COMMISSION.

Последний пример из упражнения подчеркивает, что условное выражение в проверке CHECK обрабатывается отлично от фразы WHERE (или оператора CASE). В случае CHECK изменение отвергается, когда условное выражение дает FALSE; в случае WHERE строка выбирается для обработки, когда условное выражение дает TRUE. Область расхождения в трактовке — NULL в качестве результата оценки логического выражения.

Обратите внимание, что формулировать ограничения целостности в тексте предложения CREATE TABLE допускается как на уровне столбца (если только ограничение касается единственного столбца, как, например, PK_EMP1 и CK_SAL), так и на уровне таблицы (CHECK_WHOLE_EARNING и SALE_COMMISSION). Такая разница в записи не отражается на свойствах таблицы.

Упражнение. Удалите созданную таблицу EMP1 и создайте заново, но сформулировав те же ограничения целостности на уровне таблицы.

В отличие от стандарта SQL, в Oracle условное выражение в CHECK не имеет право содержать обращения к БД (в том числе через посредство функций), что существенно ослабляет его общую силу.