Определение ссылочной целостности столбца
Спецификация

См. спецификацию пункта «Создание таблицы».

Общие правила
  1. Конструкция < ссылочная целостность столбца > определяет, что добавляемое (обновляемое) значение данного столбца должно совпадать с одним из значений указанного в REFERENCES столбца или иметь NULL-значение.

  2. Для исключения неоднозначности указанный в REFERENCES столбец должен быть столбцом типа PRIMARY KEY.

    create or replace table test1 (col1 int autorowid primary key);
    
    create or replace table test2 (col1 int references test1(col1));
    
    insert into test1;
    insert into test1;
    insert into test1;
    select * from test1;
    1
    2
    3
    
    insert into test2(col1)  values (2)
    insert into test2(col1)  values (2)
    select * from test2;
    2
    2
    
    insert into test2(col1)  values (5);
    //ошибка – не существует значение первичного ключа
  3. Допускается ссылаться на столбцы в той же самой таблице.

    create or replace table test
    (col1 int primary key,
     col2 int references test(col1),
     col3 char(10) unique,
     col4 char(20) references test(col3));
  4. Если < имя столбца > не указано, предполагается, что ссылка делается на столбец с атрибутом PRIMARY KEY в < спецификации таблицы >.

    create or replace table t_pk (c_pk varchar(5) primary key);
    create or replace table t_fk (c_fk varchar(5) references t_pk);
  5. Разрешается не указывать столбец в конструкции REFERENCES, если ссылка производится на первичный ключ таблицы.

    create or replace table tab1 (i int, c1 char(10), c2 varchar(15), primary key (i,c2));
    create or replace table tab2 (i int, c2 varchar(20), foreign key(i,c2) references tab1);
  6. Числовые типы данных столбцов, задействованных в < ссылочной целостности столбца >, должны быть идентичными.

  7. Длина символьных или байтовых данных определяемого столбца должна быть не больше длины данных столбца, на который делается ссылка.

    Допустимые команды
    create or replace table t_pk (c_pk varchar(5) primary key);
    create or replace table t_fk (c_fk varchar(10) references t_pk);
    
    Недопустимые команды
    create or replace table t_pk (c_pk varchar(200) primary key);
    create or replace table t_fk (c_fk varchar(10) references t_pk);
  8. < Каскадные ограничения > определяют действия, которые должны выполняться при попытке удалить или обновить значение столбца, на которое есть ссылка:

    • ON DELETE SET NULL: при удалении записи, содержащей первичный ключ, на который есть внешние ссылки, значение внешних ключей в подчиненных таблицах заменяется на NULL-значение. Чтобы выполнялось это ограничение, столбец внешнего ключа должен допускать NULL-значение.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int references t_pk(c_pk) on delete set null);
      insert into t_pk values(10), (20), (30);
      select * from t_pk;
      10
      20
      30
      
      insert into t_fk values(20), (30);
      select * from t_fk;
      20
      30
      
      delete from t_pk where c_pk=20;
      null
      30
    • Опция ON UPDATE указывает, какое значение должно быть присвоено столбцу данных типа DATE при операции обновления записи в случае отсутствия корректируемого значения и спецификации < подставляемое значение >.

      Примеры.
      а)
      create or replace table test (col1 int, col2 date on update CURRENT_TIMESTAMP);
      insert into test(col1) values (1);
      insert into test(col1, col2) values(2, to_date('11.11.1111', 'dd.mm.yyyy'));
      select * from test;
      |1  |21.11.2017:15:15:50|
      |2  |11.11.1111:15:15:50|
      
      update test set col1=200 where col1=2;
      select * from test;
      |1   |21.11.2017:15:15:50|
      |200 |21.11.2017:15:15:50|
      
      б)
      create or replace table test (i int, date1 date, date2 date on update sysdate);
      insert into test (i, date1, date2) values (1, to_date('01.01.2007','dd.mm.yyyy'), to_date('28.04.1950', 'dd.mm.yyy'));
      select * from test;
      | 1|01.01.2007:00:00:00.00|28.04.1950:00:00:00.00|
      
      select sysdate;
      |27.08.2007:15:58:31.00|
      
      update test set i = 100, date1 = to_date('11.11.1111', 'dd.mm.yyyy');
      select * from test;
      | 100|11.11.1111:00:00:00.00|27.08.2007:15:58:31.00|
    • ON UPDATE SET NULL: указывает, что при попытке обновить ключевое значение, на которое ссылаются внешние ключи в строках других таблиц, все значения, составляющие эти внешние ключи, должны быть изменены на NULL. Чтобы выполнялось это ограничение, все столбцы внешних ключей целевой таблицы должны допускать значение NULL.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int references t_pk(c_pk) on update set null);
      insert into t_pk values(10), (20), (30);
      select * from t_pk;
      10
      20
      30
      
      insert into t_fk values(20), (30);
      select * from t_fk;
      20
      30
      
      update t_pk set c_pk=500 where c_pk=20
      select * from t_pk;
      10
      500
      30
      
      select * from t_fk;
      null
      30
    • ON DELETE SET DEFAULT: Указывает, что при удалении значения, на которое ссылается внешний ключ, оно должно быть заменено на значение по умолчанию. Чтобы выполнялось это ограничение, столбец внешнего ключа должен допускать NULL-значение.

      Задаваемое значение по умолчанию должно присутствовать среди значений первичного ключа.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int default -1 references t_pk(c_pk) on delete set default);
      insert into t_pk values(10), (20), (30),(-1);
      insert into t_fk values(20), (30);
      delete from t_pk where c_pk=20;

      Если столбец допускает NULL-значение и значение по умолчанию явно не задано, то NULL-значение становится неявным значением по умолчанию для данного столбца.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int references t_pk(c_pk) on delete set default);
      insert into t_pk values(10), (20), (30);
      insert into t_fk values(20), (30);
      delete from t_pk where c_pk=20;
    • ON UPDATE SET DEFAULT: указывает, что при изменении значения, на которое ссылается внешний ключ, оно должно быть заменено на значение по умолчанию. Чтобы выполнялось это ограничение, столбец внешнего ключа должен допускать NULL-значение.

      Задаваемое значение по умолчанию должно присутствовать среди значений первичного ключа.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int default -1 references t_pk(c_pk) on update set default);
      insert into t_pk values(10), (20), (30), (-1);
      select * from t_pk;
      |10|
      |20|
      |30|
      |-1|
      
      insert into t_fk values(20), (30);
      select * from t_fk;
      |20|
      |30|
      C
    • ON DELETE CASCADE: При удалении в родительской таблице записи с первичным ключом одновременно удаляется все записи в подчиненных таблицах, ссылающиеся на удаляемый первичный ключ.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk1 (c_fk int references t_pk(c_pk) on delete cascade);
      create or replace table t_fk2 (c_fk int references t_pk(c_pk) on delete cascade);
      insert into t_pk values(10), (20), (30);
      select * from t_pk;
      |10|
      |20|
      |30|
      
      insert into t_fk1 values(20), (30);
      select * from t_fk1;
      |20|
      |30|
      
      insert into t_fk2 values(10),(20);
      select * from t_fk2;
      |10|
      |20|
      
      delete  from t_pk where c_pk=20;
      select * from t_pk;
      |10|
      |30|
      
      select * from t_fk1;
      |30|
      
      select * from t_fk2;
      |10|
    • ON UPDATE CASCADE: при изменении в родительской таблице значения первичного ключа одновременно во всех подчиненных таблицах заменяетcя значение столбца, ссылающегося на изменяемый первичный ключ.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk1 (c_fk int references t_pk(c_pk) on update cascade);
      create or replace table t_fk2 (c_fk int references t_pk(c_pk) on update cascade);
      insert into t_pk values(10), (20), (30);
      select * from t_pk;
      |10|
      |20|
      |30|
      
      insert into t_fk1 values(20), (30);
      select * from t_fk1;
      |20|
      |30|
      
      insert into t_fk2 values(10),(20);
      select * from t_fk2;
      |10|
      |20|
      
      update t_pk set c_pk=500 where c_pk=20;
      select * from t_pk;
      | 10|
      |500|
      | 30|
      
      select * from t_fk1;
      |500|
      | 30|
      
      select * from t_fk2;
      | 10|
      |500|
    • ON DELETE NO ACTION|RESTRICT: прекратить операцию, если её выполнение приводит к нарушению ссылочной целостности (с выдачей соответствующего кода завершения). Ограничение RESTRICT является синонимом NO ACTION.

      create or replace table t_pk (c_pk int primary key);
      create or replace table t_fk (c_fk int references t_pk(c_pk) on delete no action);
      
      insert into t_pk values(10), (20), (30);
      select * from t_pk;
      
      insert into t_fk values(20), (30);
      select * from t_fk;
      delete from t_pk where c_pk=20;
      /* существует соответствующее значение внешнего ключа */
  9. Разрешается удалять строки со спецификацией действия NO ACTION (RESTRICT) и ссылающиеся на самих себя.

  10. Если < спецификация действий > не задана, по умолчанию предполагается NO ACTION.

  11. Спецификация действий:

    • CASCADE: строка с первичным (уникальным) ключом в родительской таблице удаляется/изменяется, и если в определении ограничения внешнего ключа отсутствует опция MATCH или присутствуют спецификации MATCH SIMPLE или MATCH FULL, то удаляются/изменяются все строки в подчиненной таблице, ссылающиеся на удаляемую/изменяемую строку родительской таблицы. Если же в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то удаляются/изменяются только те строки подчиненной таблицы, которые ссылаются исключительно на удаляемую/изменяемую строку родительской таблицы;

    • SET NULL: строка с первичным (уникальным) ключом в родительской таблице удаляется/изменяется. Во всех столбцах, которые входят в состав внешнего ключа, и во всех строках подчиненной таблицы, ссылающихся на удаленную/измененную строку родительской таблицы, проставляется NULL-значение. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то NULL-значение устанавливается только в тех строках подчиненной таблицы, которые ссылаются исключительно на удаляемую/изменяемую строку родительской таблицы;

    • SET DEFAULT: строка с первичным (уникальным) ключом в родительской таблице удаляется/изменяется. Во всех столбцах, которые входят в состав внешнего ключа, и во всех строках подчиненной таблицы, ссылающихся на удаленную/измененную строку родительской таблицы, проставляется заданное при их определении значение по умолчанию. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то значение по умолчанию устанавливается только в тех строках подчиненной таблицы, которые ссылаются исключительно на удаляемую/изменяемую строку родительской таблицы;

    • NO ACTION: прекратить операцию (с выдачей соответствующего кода завершения), если её выполнение приводит к нарушению ссылочной целостности (ограничение RESTRICT является синонимом NO ACTION).

      create or replace table test1(i1 int, j1 int, primary key(i1,j1));
      insert into test1 values (1,1);
      create or replace table test2(i2 int, j2 int, foreign key (i2,j2) references test1(i1,j1) match simple on delete cascade);
      insert into test2 values(1,NULL);
      insert into test2 values(NULL,4);
      insert into test2 values(1,1);
      insert into test2 values(NULL,NULL);
      select * from test2;
       I2          J2
       --          --
      |          1|           |
      |           |          4|
      |          1|          1|
      |           |           |
      
      
      delete from test1;
      select * from test2;
      I2          J2
       --          --
      |          1|           |
      |           |          4|
      |           |           |
  12. Список столбцов спецификации PRIMARY KEY в < ограничении таблицы > определяет составной первичный ключ таблицы. Значения отдельных столбцов многостолбцового первичного ключа могут повторяться, однако в каждой строке таблицы конкатенация значений столбцов составного ключа должна быть уникальной.

    create or replace table tab1 (vc varchar(30) not null, primary key (vc));