12 нояб. 2010 г.

Поиск дублирующихся FOREIGN KEY

Стандарт SQL запрещает создание более одного первичного ключа для таблицы, а вот количество внешних ключей по одному и тому же полю никак не ограничивается. Хотя ясно, что свыше одного — это пустая нагрузка на сервер и лишний индекс в базе данных. Запрос ниже помогает найти дублирующиеся внешние ссылки.
SELECT
  rf.rdb$relation_name,
  rf.rdb$field_name,
  LIST(TRIM(rc.rdb$constraint_name)),
  COUNT(*)
FROM
  rdb$indices i
  JOIN rdb$index_segments iseg 
    ON iseg.rdb$index_name = i.rdb$index_name
  JOIN rdb$relation_constraints rc 
    ON rc.rdb$index_name = i.rdb$index_name
      AND rc.rdb$constraint_type = 'FOREIGN KEY'
  JOIN rdb$relation_fields rf 
    ON rf.rdb$relation_name = i.rdb$relation_name
      AND iseg.rdb$field_name = rf.rdb$field_name
GROUP BY
  1, 2
HAVING
  COUNT(*) > 1
Labels:

2 комментария:

Unknown комментирует...

У меня в базе запрос выдал 2 позиции
1)RDB$RELATION_NAME
2)RDB$FIELD_NAME
3)LIST
4)COUNT

1.
1) = AC_TRRECORD
2) = ACCOUNTKEY
3) = FK_AC_TRRECORD_ACCOUNTKEY,AC_FK_TRRECORD_AK
4) = 2

2.
1) = AT_RELATIONS
2) = BRANCHKEY
3) = AT_FK_RELATIONS_BRANCHKEY,AT_KK_RELATIONS_BRANCHKEY
4) = 2

Т.е. в таблице AC_TRRECORD есть 2 индекса по одному полю ACCOUNTKEY. Я нашел эти индексы. Это
RDB$FOREIGN893 и RDB$FOREIGN893. Попытка даже не удалить, а сделать неактивным один из них не удалась. IBExpert выдал
This operation is not defined for system tables.
unsuccessful metadata update.

MODIFY RDB$INDICES failed.
action cancelled by trigger (2) to preserve data integrity.
Cannot deactivate index used by an integrity constraint.
Как исправить ситуацию? Как избавиться от лишних индексов?

15 ноября 2010 г. в 08:30
Andrei комментирует...

"Я нашел эти индексы. Это
RDB$FOREIGN893 и RDB$FOREIGN893."

Это системные индексы, которые являются частью ограничения FOREIGN KEY. Чтобы их удалить, следует удалить соответствующий FOREIGN KEY.

Вручную можно сделать так:

SELECT * FROM rdb$relation_constraints
WHERE rdb$index_name = 'RDB$FOREIGN893'

Этим запросом вы определите имя таблицы и имя ограничения. Далее следует написать команду:

ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения

Если под рукой есть IBExpert, то откройте свойства таблицы AC_TRRECORD, на вкладке FOREIGN KEYS установите курсор на нужный ключ и по правой кнопке мыши выберите команду DROP.

15 ноября 2010 г. в 10:50

Отправить комментарий