ORA-02270: no matching unique or primary key for this column-list

[Unanonimized]

Когда ограничение внешнего ключа включено без проверки, это запрещает изменять данные в таблице.
Это вроде бы плохо.

Да ладно.
Вот прям запрещает?
Вот прям совсем?
На самом деле это не так работает.

Создадим таблички

Код: Выделить всё

create table dropme_t(
  id number
     constraint dropme_t#pk primary key
     deferrable initially immediate
     using index (create index dropme_t$id on dropme_t(id) unusable)
--     disable
  )
/
Table created

ALTER TABLE dropme_t  DISABLE CONSTRAINT dropme_t#pk
/
Table altered

ALTER INDEX dropme_t$id UNUSABLE
/
Index altered

create table dropme_t_child(
  id_t number constraint dropme_t_child#fk references dropme_t(id)
       disable
  )
/
Table created

create index dropme_t_child$id_t on dropme_t_child(id_t) unusable
/
Index created

Итак, нам выделено "окно" на production сервере.
Начальство стоит над плечом и каждые три минуты интересуется, когда уже когда.
Работаем в условиях стресса.
Исходное состояние: ограничения целостности выключены, индексы unusable

Код: Выделить всё

select constraint_name, constraint_type, status
  from user_constraints where table_name in ('DROPME_T', 'DROPME_T_CHILD')
 order by 1
;
CONSTRAINT_NAME                                                                  CONSTRAINT_TYPE STATUS
-------------------------------------------------------------------------------- --------------- --------
DROPME_T#PK                                                                      P               DISABLED
DROPME_T_CHILD#FK                                                                R               DISABLED

select index_name, status
  from user_indexes where table_name in ('DROPME_T', 'DROPME_T_CHILD')
 order by 1
;
INDEX_NAME                                                                       STATUS
-------------------------------------------------------------------------------- --------
DROPME_T$ID                                                                      UNUSABLE
DROPME_T_CHILD$ID_T                                                              UNUSABLE

Погнали.
1. Напихаем данные в родительскую, при загрузке случился дубль, но мы этого еще не знаем

Код: Выделить всё

insert into dropme_t select rownum from dual connect by level < 1000
/
999 rows inserted

-- Вот он наш дубль
insert into dropme_t values(1);
1 row inserted

Пробуем включить PK

Код: Выделить всё

ALTER TABLE dropme_t ENABLE VALIDATE CONSTRAINT dropme_t#pk
/
ORA-14063: Unusable index exists on unique/primary constraint key

Упс, а PK без индексов не работает.
Но включить надо, начальник при виде "ORA" начинает нервничать и цыкать зубом.
А если без проверки?

Код: Выделить всё

ALTER TABLE dropme_t ENABLE NOVALIDATE CONSTRAINT dropme_t#pk
/
Table altered

А-га! Скажем мы, ну вот же, работает...
Но таки нет:

Код: Выделить всё

insert into dropme_t values(1);
ORA-01502: index 'TESTUSER.DROPME_T$ID' or partition of such index is in unusable state

Придется индекс таки включить, тут без вариантов.

Код: Выделить всё

alter index dropme_t$id rebuild
/
--- Вот тут на больших БД можно идти попить кофе, а на совсем больших - еще и пообедать, но это другая история.
Index altered

Ура. Что там с PK?

Код: Выделить всё

select constraint_name, constraint_type, status, VALIDATED
  from user_constraints c where table_name in ('DROPME_T')
;
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS   VALIDATED
--------------- --------------- -------- -------------
DROPME_T#PK     P               ENABLED  NOT VALIDATED

select index_name, status
  from user_indexes where table_name in ('DROPME_T')
;
INDEX_NAME      STATUS
--------------- --------
DROPME_T$ID     VALID

Ок, индекс живой, PK живой, хоть и не валидирован.
Как это работает:

Код: Выделить всё

-- Дубль вставить больше не получится, хотя имеющиеся никуда не делись:
insert into dropme_t values(1)
ORA-00001: unique constraint (TESTUSER.DROPME_T#PK) violated

-- А вот вставка уникальных значений PK проходит без проблем:
insert into dropme_t select 1000+rownum from dual connect by level < 1000
/
999 rows inserted

Отложенная проверка на невалидированном PK работает штатно:

Код: Выделить всё

set constraint dropme_t#pk DEFERRED
/
Constraints set

insert into dropme_t select rownum from dual connect by level < 1000
/
999 rows inserted

commit
/
ORA-02091: transaction rolled back
ORA-00001: unique constraint (TESTUSER.DROPME_T#PK) violated

Как когда-то сказал Tom Kyte, enable novalidate - это способ запереть конюшню за сбежавшими лошадьми.

Теперь смотрим что там с FK

Код: Выделить всё

select constraint_name, constraint_type, status
  from user_constraints where table_name in ('DROPME_T_CHILD')
 order by 1
;
CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS
-------------------- --------------- --------
DROPME_T_CHILD#FK    R               DISABLED

select index_name, status
  from user_indexes where table_name in ('DROPME_T_CHILD')
 order by 1
;
INDEX_NAME           STATUS
-------------------- --------
DROPME_T_CHILD$ID_T  UNUSABLE

А давайте вставим! )
...в смысле - загрузим данные.

Код: Выделить всё

-- У этой записи нет родительского ключа
insert into dropme_t_child values(0)
/
1 row inserted

-- Эта запись ссылается на дублированный ключ
insert into dropme_t_child values(1)
/
1 row inserted

-- Ну бывают же просто нормальные записи в дочерних таблицах...
insert into dropme_t_child values(100)
/
1 row inserted

А теперь включаем FK.

Код: Выделить всё

-- Нормальное включение невозможно, присутствует запись, ссылающаяся в никуда.
ALTER TABLE dropme_t_child ENABLE VALIDATE CONSTRAINT dropme_t_child#FK
/
ORA-02298: cannot validate (TESTUSER.DROPME_T_CHILD#FK) - parent keys not found

-- Но без проверки можно.
ALTER TABLE dropme_t_child ENABLE NOVALIDATE CONSTRAINT dropme_t_child#FK
/
Table altered

Помним про PK, как он не работал без индекса?
Так вот, FK это не касается:

Код: Выделить всё

select constraint_name, constraint_type, status
  from user_constraints where table_name in ('DROPME_T_CHILD')
;
CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS
-------------------- --------------- --------
DROPME_T_CHILD#FK    R               ENABLED

select index_name, status
  from user_indexes where table_name in ('DROPME_T_CHILD')
;
INDEX_NAME           STATUS
-------------------- --------
DROPME_T_CHILD$ID_T  UNUSABLE

-- Ссылается на дубль
insert into dropme_t_child values(1)
/
1 row inserted

-- Ссылка мимо родительского ключа
insert into dropme_t_child values(-1)
/
ORA-02291: integrity constraint (TESTUSER.DROPME_T_CHILD#FK) violated - parent key not found

-- Просто запись. Нормальная.
insert into dropme_t_child values(100)
/
1 row inserted

commit
/
Commit complete

Теперь можно включить индекс для FK (зачем он там - отдельный разговор),
запустить пользователей в систему, отпустить начальство,
выдохнуть и заняться делами:

Код: Выделить всё

select ID from (select rowid rid, ID, lag(null,1,1) over(partition by id order by rowid) keep_me from dropme_t) where keep_me is null;
        ID
----------
         1
         1

delete dropme_t where rowid in (select rid from (select rowid rid, ID, lag(null,1,1) over(partition by id order by rowid) keep_me from dropme_t) where keep_me is null);
2 rows deleted        
         
сommit;
Commit complete
 
 ALTER TABLE dropme_t ENABLE primary key;
Table altered       

Код: Выделить всё

select rowid rid, id_t from dropme_t_child ch where not exists(select null from dropme_t p where p.id = ch.id_t);
RID                                                                                    ID_T
-------------------------------------------------------------------------------- ----------
AASw/dAApAAACXuAAA                                                                        0

delete(
  select rowid rid, id_t from dropme_t_child ch where not exists(select null from dropme_t p where p.id = ch.id_t)
);
1 row deleted

сommit;
Commit complete

ALTER TABLE dropme_t_child ENABLE constraint dropme_t_child#FK;
Table altered

alter index dropme_t_child$id_t rebuild online;
Index altered

Финал рабочей недели

Код: Выделить всё

select constraint_name, constraint_type, status
  from user_constraints where table_name in ('DROPME_T', 'DROPME_T_CHILD')
 order by 1
;

CONSTRAINT_NAME      CONSTRAINT_TYPE STATUS
-------------------- --------------- --------
DROPME_T#PK          P               ENABLED
DROPME_T_CHILD#FK    R               ENABLED

select index_name, status
  from user_indexes where table_name in ('DROPME_T', 'DROPME_T_CHILD')
 order by 1
;

INDEX_NAME           STATUS
-------------------- --------
DROPME_T$ID          VALID
DROPME_T_CHILD$ID_T  VALID

Уфф. Можно идти в кабак.

[Valery Yourinsky]

. . .

Получилось очень "много букв".
Изучу постепенно и отвечу тоже постепенно...
Спасибо!