Доступные версии документации

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

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

Синтаксические правила
  1. Столбцы, на которые делается ссылка (<имя столбца>[, …]), должны быть столбцами PRIMARY KEY или UNIQUE.

  2. <Спецификация родительской таблицы> определяет таблицу, на которую производится ссылка из данной дочерней таблицы.

    Примеры.
    1.
    Родительская таблица
    
    create or replace table "Специальности" (id int autoinc initial (100), name char(20));
    
    insert into "Специальности"(name) values('Программист');
    insert into "Специальности"(name) values('Инженер');
    insert into "Специальности"(name) values('Дизайнер');
    select * from  "Специальности";
    |ID |NAME             |
    |100|Программист      |
    |101|Инженер          |
    |100|Дизайнер         |
    
    Дочерняя таблица
    
    create or replace table "Штатное расписание" (id_person int autoinc, fio char(20), job int references "Специальности"(id_job));
    
    insert into "Штатное расписание"(fio, job) values('Иванов И.И.', 102);
    insert into "Штатное расписание"(fio, job) values('Петров П.П.', 100);
    select * from  "Штатное расписание";
    |ID_PERSON|FIO          |JOB|
    |1        |Иванов И.И.  |100|
    |2        |Петров П.П.  |102|
    
    либо
    create or replace table "Штатное расписание" (id_person int autoinc, fio char(20),job int references "Специальности"(id_job));
    insert into "Штатное расписание"(fio, job) values('Иванов И.И.', (select id_job from "Специальности" where name='Программист'));
    insert into "Штатное расписание"(fio, job) values('Петров П.П.', (select id_job from "Специальности" where name='Дизайнер'));
    |ID_PERSON|FIO          |JOB|
    |1        |Иванов И.И.  |100|
    |2        |Петров П.П.  |102|
    
    Ссылка на составной внешний ключ
    Для исключения неоднозначности при полном совпадении ФИО вводятся дополнительные значения: год и место рождения.
    
    create or replace table "Штатное расписание" 
    (id_person int autoinc, 
     fio char(20),
     year int, 
     birth_place char(10), 
     job int  references "Специальности"(id_job))
    
    insert into "Штатное расписание"(fio, year, birth_place, job)
    values('Иванов И.И.', 1998, 'Воронеж', 100);
    insert into "Штатное расписание"(fio, year, birth_place, job)
    values('Иванов И.И.', 1998, 'Москва', 102);
    insert into "Штатное расписание"(fio, year, birth_place, job)
    values('Петров П.П.', 1995, 'Воронеж', 100);
    select * from  "Штатное расписание";
    |ID_PERSON|FIO         |YEAR|BIRTH_PLAC      | JOB  |
    |1        |Иванов И.И. |1998|Воронеж         | 100  |
    |2        |Иванов И.И. |1998|Москва          | 102  |
    |3        |Петров П.П  |1995|Воронеж         | 100  |
    
    create table ref_sub (id int primary key, name char(10), id_ref int references par_tab(id_key));
    
    2.
    Ссылка на столбец в той же таблице:
    create table tab1 (i int primary key, c char(10), ref_i int references tab1(i));
  3. Разрешается не указывать список столбцов в конструкции REFERENCES, если ссылка производится на первичный ключ родительской таблицы.

    create or replace table tab1 (i int, c1 char(10), c2 varchar(15), primary key primary key (i, c2));
    create or replace table tab2 (i int, c2 varchar(20), foreign key(i, c2) references tab1);
  4. В спецификации ссылки REFERENCES <спецификация родительской таблицы> и <имена столбцов> определяют имя таблицы и имена столбцов в этой таблице, на которые производится ссылка из таблицы объявляемого столбца (или столбцов).

    create table ref_sub (id int primary key, name char(10), id_ref int references par_tab(id_key));
    
    Ссылка на столбец в той же таблице:
    create table tab1 (i int primary key, c char(10), ref_i int references tab1(i));
  5. <Спецификация схемы таблицы> задает имя таблицы, на столбец (или столбцы) которой ссылается <внешний ключ>.

Общие правила
  1. Опция MATCH задает ссылочную целостность с расширенными сравнениями ключей. Ссылочная целостность удовлетворяется (результат сравнения TRUE), если внешний ключ определяемой (дочерней) таблицы совпадает по заданному критерию с одним из первичных (уникальных) ключей главной (родительской) таблицы, в противном случае – FALSE Пусть в описании ниже имя некоторой таблицы БД будет T, имя определяемой таблицы S.

  2. Тип сравнения SIMPLE или тип сравнения не задан (используется по умолчанию). Ограничение внешнего ключа (ссылочное ограничение) удовлетворяется в том и только в том случае, когда для каждой записи таблицы S выполняется одно из условий:

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

      |PK (таблица T)|FK (таблица S)|Ссылочная целость удовлетворяется?|
      |10, aaa       |10, aaa       |Да (есть PK= 10, aaa)             |
      |20, bbb       |55, bbb       |Нет (нет соответствующего PK)     |
      |10, bbb       |10, bbb       |Да (есть PK= 10, bbb)             |
      |20, aaa       |20,aaa        |Да (есть PK= 20, aaa)             |
      |10, ddd       |              |Нет (нет соответствующего PK)     |
      
      create or replace table t_pk (c_pk1 int, c_pk2 decimal, c_pk3 char(10), primary key(c_pk1, c_pk3));
      
      create or replace table t_fk (c_fk1 int, c_fk2 decimal, c_fk3 char(10), foreign key (c_fk1, c_fk3) references t_pk(c_pk1, c_pk3)  match simple);
      
      insert into t_pk (c_pk1, c_pk2, c_pk3) values 
      (10, 25.7,'aaa'),
      (20, 67.5,'bbb'),
      (10, 219.6, 'bbb'),
      (20, 66.8, 'aaa');
      
      select * from t_pk;
      C_PK1 C_PK2 C_PK3
      10    25.7  aaa
      20    67.5  bbb
      10    219.6 bbb
      20    66.8  aaa
      
      insert into t_fk (c_fk1, c_fk2, c_fk3)
      values (10, -5.7, 'bbb'), (20, 409.8, 'aaa');
      select * from t_fk;
      C_FK1 C_FK2 C_FK3
      10    -5.7  bbb
      20    409.8 aaa
      
      Ошибка: нет совпадения со значениями первичного составного ключа
      insert into t_fk (c_fk1, c_fk2, c_fk3) values
      (55, -5.7, 'bbb'), (20, 409.8, 'aaa');
    • какой-либо столбец, входящий в состав внешнего ключа определяемой таблицы, содержит NULL-значение.

      PK (таблица T) FK (таблица S) Ссылочная целость удовлетворяется?
      20, bbb        null, bbb      Да (есть PK= bbb, null в FK)
      30, ccc        null, eee      Да (есть null в FK)
                     30, null       Да (есть PK= 30, null в FK)
                     null, null     Да (есть null в FK)
                     30, ссс        Да (есть PK=30, ccc)
                     40, ddd        Нет (нет PK=4, ddd и нет null в FK)
      
      insert into t_fk (c_fk1, c_fk2, c_fk3)
      values(null, -5.7, ‘bbb’l), (20,409.8, 'aaa');
      select * from t_fk;
      
      insert into t_fk (c_fk1, c_fk2, c_fk3)
      values(null, -5.7, null), (20,409.8, 'aaa');
      select * from t_fk;
  3. Тип сравнения MATCH PARTIAL. Ограничение внешнего ключа (ссылочное ограничение) удовлетворяется в том и только в том случае, когда для каждой записи таблицы S выполняется одно из условий:

    • таблица T содержит по крайней мере одну такую запись, что значение каждого столбца данной записи таблицы S, отличное от NULL-значения, совпадает со значением соответствующего столбца возможного ключа в этой записи таблицы T.

      PK (таблица T)  FK (таблица S)
      Ссылочная целость удовлетворяется?
      10, aaa, 15.6   10, aaa, 15.6
      Да (есть соответствующий первичный ключ)
      20, bbb, 34.7   20, abc, 34.7
      Нет (нет соответствующего первичного ключа)
      30, ccc, 78.3   40, bbb, 77.8
      Нет (нет соответствующего первичного ключа)
    • каждый столбец, входящий в состав внешнего ключа, содержит NULL-значение.

      PK (таблица T)  FK (таблица S)
      Ссылочная целость удовлетворяется?
      10, aaa, 15.6   20, null, null
      Да (есть частичное совпадение: 20)
      20, bbb, 34.7   20, null, -3
      Нет (отсутствует частичное совпадение для 20, -3)
      30, ccc, 78.3   null, bbb, 45.6
      Нет (отсутствует частичное совпадение для bbb, 45.6)
                      null, null,null
      Да (игнорируется совпадение с первичным ключом)
                      20, abc, null
      Нет (отсутствует частичное совпадение для 20, abc)
                      40, null, null
      Нет (отсутствует частичное совпадение для 40)
                      40, ddd, null
      Нет (отсутствует частичное совпадение для 40, ddd)
      
      create or replace table t_pk (c_pk1 int,
      c_pk2 char(10), c_pk3 decimal, primary key(c_pk1, c_pk2, c_pk3));
      
      insert into t_pk (c_pk1, c_pk2, c_pk3) values 
      (10,'aaa', 15.6),
      (20, 'bbb', 34.7),
      (30, 'ccc', 78.3);
      
      select * from t_pk;
      C_PK1 C_PK2 C_PK3
      10    aaa   15.6
      20    bbb   34.7
      30    ccc   78.3
      
      create or replace table t_fk (c_fk1 int, c_fk2 char(10), c_fk3 decimal, foreign key (c_fk1, c_fk2, c_fk3) references t_pk(c_pk1, c_pk2, c_pk3)  match partial);
      
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(10, 'aaa', 15.6);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(20, 'abc' 34.7);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(40, 'bbb' 77.8);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(20, null, null);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(20, null, -3);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(null, null, null);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(20, 'abc', null);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(40, null, null);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(40, 'ddd', null);
      
      select * from t_fk;
      
      C_FK1 C_FK2 C_FK3
      10    aaa   15.6
      20    NULL  NULL
      NULL  NULL  NULL
  4. Тип сравнения MATCH FULL. Ограничение внешнего ключа (ссылочное ограничение) удовлетворяется в том и только в том случае, когда для каждой записи таблицы S выполняется одно из условий:

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

      PK (таблица T)  FK (таблица S)
      Ссылочная целость удовлетворяется?
      10, aaa, 15.6 10, aaa, 15.6
      Да (полное совпадение с первичным ключом)
      20, bbb, 34.7 20, bbb, null
      Нет (отсутствует совпадение с первичным ключом)
      30, ccc, 78.3 null, null, 78.3
      Нет (отсутствует совпадение с первичным ключом)
      
      create or replace table t_pk (c_pk1 int, c_pk2 char(10), c_pk3 decimal, primary key(c_pk1, c_pk2, c_pk3));
      
      insert into t_pk (c_pk1, c_pk2, c_pk3) values 
      (10,'aaa', 15.6),
      (20, 'bbb', 34.7),
      (30, 'ccc', 78.3);
      
      select * from t_pk;
      C_PK1 C_PK2 C_PK3
      10    aaa   15.6
      20    bbb   34.7
      30    ccc   78.3
      
      create or replace table t_fk (c_fk1 int, c_fk2 char(10), c_fk3 decimal, foreign key (c_fk1, c_fk2, c_fk3) references t_pk(c_pk1, c_pk2, c_pk3)  match full);
      
      
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(10, 'aaa', 15.6);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(20, 'bbb', null);
      insert into t_fk (c_fk1, c_fk2, c_fk3)values(null, null, 78.3);
      
      select * from t_fk;
      C_FK1 C_FK2 C_FK3
      10    aaa   15.6
    • каждый столбец, входящий в состав внешнего ключа, содержит NULL-значение.

      PK (таблица T) FK (таблица S)
      Ссылочная целость удовлетворяется?
      10, aaa, 15.6  null, null, null
      Да (совпадение значений внешнего и первичного ключа не требуется)
      20, bbb, 34.7
      30, ccc, 78.3
  5. <Спецификация действия> задает действие, которое должно быть выполнено с записью, содержащей внешний ключ (в дочерней таблице) при удалении/изменении первичного (уникального) ключа в родительской таблице:

    • CASCADE: если запись с первичным (уникальным) ключом в родительской таблице удаляется/изменяется, то удаляются/изменяются и все записи в дочерней таблице, ссылающиеся на удаляемую/изменяемую запись родительской таблицы при условии, что в <ссылочной целостности>:

      • отсутствует опция MATCH;

      • либо присутствуют опции MATCH SIMPLE или MATCH FULL.

      Если же в <ссылочной целостности> указана опция MATCH PARTIAL, то удаляются/изменяются только те записи дочерней таблицы, которые ссылаются исключительно на удаляемую/изменяемую запись родительской таблицы.

      Т.е. при <спецификация действия> CASCADE действия с записью в родительской таблице (удаление/изменение) полностью повторяется с записями в дочерних таблицах.

      create or replace table "Родительская"
      (i int autorowid,
       pk_col1 int autoinc initial (50),
       d decimal default 10.5,
       pk_col2 char(5), primary key (pk_col1, pk_col2));
      insert into "Родительская"(pk_col2) values ('11111'),('22222'), ('33333');
      select * from "Родительская";
      
      I  PK_COL1   D     PK_COL2
      1  50        10.5  11111
      2  51        10.5  22222
      3  52        10.5  33333
      
      create or replace table "Дочерняя"
      (vc varchar(10) default 'aaaaa',
       fk_col1 int,
       col2 decimal default 45.8,
       fk_col3 char(5),
      foreign key (fk_col1,fk_col3)
      references "Родительская"(pk_col1, pk_col2) on delete cascade);
      
      insert into "Дочерняя"(fk_col1, fk_col3)
      values (50,'11111'), (51,'22222'), (52,'33333');
      select * from "Дочерняя";
      VC      FK_COL1   COL2  FK_COL3
      aaaaa   50        45.8  11111
      aaaaa   51        45.8  22222
      aaaaa   52        45.8  33333
      
      delete from "Родительская" where i=2;
      select * from "Родительская";
      
      I  PK_COL1  D     PK_COL2
      1  50       10.5  11111
      3  52       10.5  33333
      
      select * from "Дочерняя";
      VC      FK_COL1   COL2   FK_COL3
      aaaaa   50        45.8   11111
      aaaaa   52        45.8   33333
    • SET NULL: если запись с первичным (уникальным) ключом в родительской таблице удаляется/изменяется, то во всех столбцах, которые входят в состав внешнего ключа, и во всех записях дочерней таблицы, ссылающихся на удаленную/измененную запись родительской таблицы, проставляется NULL-значение. Если в <ссылочной целостности> содержится опция MATCH PARTIAL, то NULL-значение устанавливается только в тех записях дочерней таблицы, которые ссылаются исключительно на удаляемую/изменяемую запись родительской таблицы.

      Т.е. записям дочерней таблицы будет присвоено NULL-значение при удалении/изменении соответствующих записей родительской таблицы. Правило будет выполняться, если поля дочерней таблицы допускают NULL-значения.

      Спецификацию таблицы "Родительская" и её содержимое
      см. в описании действия CASCADE.
      select * from "Родительская";
      
      I  PK_COL1   D     PK_COL2
      1  50        10.5  11111
      2  51        10.5  22222
      3  52        10.5  33333
      
      create or replace table "Дочерняя"
      (vc varchar(10) default 'aaaaa',
       fk_col1 int,
       col2 decimal default 45.8,
       fk_col3 char(5),
      foreign key (fk_col1,fk_col3) references "Родительская"(pk_col1, pk_col2) on update set null);
      
      insert into "Дочерняя"(fk_col1, fk_col3)
      values (50, '11111'), (51, '22222'), (52, '33333');
      select * from "Дочерняя";
      
      update "Родительская" set pk_col2='zzzzz' where i=2;
      select * from "Родительская";
      I  PK_COL1  D     PK_COL2
      1  50       10.5  11111
      2  51       10.5  zzzzz
      3  52       10.5  33333
      select * from "Дочерняя";
      VC      FK_COL1   COL2   FK_COL3
      aaaaa   50        45.8   11111
      aaaaa   NULL      45.8   NULL
      aaaaa   52        45.8   33333
    • SET DEFAULT: если запись с первичным (уникальным) ключом в родительской таблице удаляется/изменяется, то во всех столбцах, которые входят в состав внешнего ключа, и во всех записях дочерней таблицы, ссылающихся на удаленную/измененную запись родительской таблицы, проставляется заданное при определении их столбцов значение по умолчанию. Комбинация всех подставляемых по умолчанию значений внешнего ключа должна соответствовать одному из первичных ключей (ключу по умолчанию). Если в определении внешнего ключа содержится опция MATCH PARTIAL, то значение по умолчанию устанавливается только в тех записях дочерней таблицы, которые ссылаются исключительно на удаляемую/изменяемую запись родительской таблицы.

      Т.е. в столбец (столбцы) записей дочерней таблицы заносятся значения по умолчанию, указанные при <спецификации определении столбцов> в родительской таблице. Если значение по умолчанию не определено, то операция удаление/изменения записи отменяется и возвращается соответствующий код завершения.

      Спецификацию таблицы "Родительская" и её содержимое
      см. в описании действия CASCADE.
      select * from "Родительская";
      
      I   PK_COL1  D      PK_COL2
      1   50       10.5   11111
      2   51       10.5   22222
      3   52       10.5   33333
      
      Комбинация значений по умолчанию 52, '33333' должна быть одним
      из значений первичного ключа (ключа по умолчанию)
      create or replace table "Дочерняя"
      (vc varchar(10) default 'aaaaa',
       fk_col1 int default 52,
       col2 decimal default 45.8,
       fk_col3 char(5) default '33333',
      foreign key (fk_col1,fk_col3) references "Родительская"(pk_col1, pk_col2) on update set default);
      
      insert into "Дочерняя"(fk_col1, fk_col3)
      values (50, '11111'), (51,'22222'), (52, '33333');
      select * from "Дочерняя";
      VC      FK_COL1   COL2  FK_COL3
      aaaaa   50        45.8  11111
      aaaaa   51        45.8  22222
      aaaaa   52        45.8  33333
      
      update "Родительская" set pk_col2='zzzzz' where i=2;
      select * from "Родительская";
      
      I   PK_COL1   D      PK_COL2
      1   50        10.5   11111
      2   51        10.5   zzzzz
      3   52        10.5   33333
      
      select * from "Дочерняя";
      VC      FK_COL1   COL2   FK_COL3
      aaaaa   50        45.8   11111
      aaaaa   52        45.8   33333
      aaaaa   52        45.8   33333
    • NO ACTION: прекратить операцию (с выдачей соответствующего кода завершения), если её выполнение приводит к нарушению ссылочной целостности (ограничение RESTRICT является синонимом NO ACTION).

      Т.е. <спецификация действия> NO ACTION запрещает удаление/изменение записи в родительской таблице при наличии связанных с ней записей в дочерней таблице. NO ACTION действует по умолчанию, если опции ON UPDATE и ON DELETE не заданы.

      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|
      |           |           |
  6. Разрешается удалять записи со спецификацией действия NO ACTION (RESTRICT) и ссылающиеся на самих себя.

  7. Определить <внешний ключ> можно как непосредственно при создании дочерней таблицы, так и позднее, c помощью оператора ALTER TABLE … ADD FORIEGN KEY... В обоих случаях родительская таблица должна быть предварительно создана.

Заметили ошибку?
Выделите текст и нажмите Ctrl + Enter