Перейти к содержимому

Невозможно удалить объект так как на него ссылается ограничение foreign key

  • автор:

Невозможно усечь таблицу, на которую ссылается ограничение внешнего ключа

Невозможно обрезать таблицу, на которую ссылается ограничение внешнего ключа. Что я должен сделать первым, чтобы включить транкат?

ditails: ( guacamole . guacamole_connection_history , CONSTRAINT guacamole_connection_history_ibfk_2 ИНОСТРАННЫЙ КЛЮЧ ( connection_id ) ССЫЛКИ guacamole . guacamole_connection ( connection_id ))

Я хочу очистить таблицу guacamole_connection для тестирования разработки.

Изображение 52429

4 ответа

Почему бы не добавить ограничение, добавляющее ON DELETE CASCADE и ON UPDATE CASCADE ? Тогда все, что вам нужно сделать, это TRUNCATE guacamole_connection CASCADE

Пример: ALTER TABLE guacamole_connection_history ADD CONSTRAINT guacamole_connection_history_cascade_delete FOREIGN KEY (connection_id) REFERENCES guacamole_connection (connection_id) ON UPDATE CASCADE ON DELETE CASCADE;

Затем просто запустите TRUNCATE guacamole_connection CASCADE

Вы можете выполнить усечение, пропуская проверки внешнего ключа.

TRUNCATE не эквивалентен DELETE: TRUNCATE это DDL-операции, в то время как DELETE является операцией DML. Другими словами, TRUNCATE изменяет структуру таблицы (т.е. Освобождает память и изменяет другие свойства в зависимости от RDBMS, над которой вы работаете), в то время как DELETE просто модифицирует данные при выполнении каждой проверки, указанной вашей моделью (т.е. Для ограничения ключей, проверки ограничений и т.д..)

Почему вы хотите обрезать таблицу? Ну, это быстрее, так как не нужно выполнять какую-либо проверку (то, почему ваш FK влияет на операцию усечения), и позволяет вам освободить все пространство, которое в настоящее время распределяет таблица (и ее индекс).

Итак, если вы хотите усечь свою таблицу, вы должны:

  • DISABLE related FK
  • TRUNCATE все связанные таблицы.
  • Включить ранее отключенные FK

Внутренне операция должна быть (снова в зависимости от СУБД), эквивалентной DROP и СОЗДАТЬ FK. Разница обычно связана с разрешениями, необходимыми, поскольку концептуально это не то же самое, что создавать/удалять FK, чем включать/отключать его

SQL-Ex blog

В рамках интерактивного (чистого) SQL избежать возникновения ошибки в этом случае не получалось. Однако проблема легко решается процедурно (T-SQL):

Встроенная функция object_id возвращает идентификатор объекта, заданного именем, или же NULL, если такого имени не существует.
И вот в SQL Server 2017 появился оператор DROP TABLE IF EXISTS, который не вызывает ошибки, если удаляемой таблицы не существует, например:

Теперь предположим, что нам нужно удалить связанные таблицы. Рассмотрим следующий пример:

id
1
2
3
id t1_id
1 1
2 2

Стандартное каскадное удаление

работать не будет, т.к. оно еще не реализовано в SQL Server (но работает, например, в PostgreSQL), тогда как оператор

вызовет следующую ошибку:
Невозможно удалить объект «t1», так как на него ссылается ограничение FOREIGN KEY.
Мы можем удалить связанные таблицы одним оператором, перечислив их через запятую (сначала подчиненную таблицу, а затем — главную):

Если указать таблицы в неправильном порядке, т.е. t1, t2, то при удалении t1 будет получена вышеприведенная ошибка, а потом будет удалена t2.
Если среди перечисленных таблиц (не обязательно две) могут быть отсутствующие, то тогда поможет новый оператор:

Заметим, что оператор DROP . IF EXISTS может применяться и к другим объектам базы данных, например, представлениям и хранимым процедурам.
Возможно, что в следующих версиях SQL Server может появиться и оператор типа:

который будет создавать таблицу только в том случае, если такой не существует.

Не удалось удалить объект dbo.Table1, поскольку на него ссылается ограничение FOREIGN KEY

Msg 3726, уровень 16, состояние 1, строка 2 Не удалось удалить объект dbo.Table1, поскольку на него ссылается ограничение FOREIGN KEY.

Использование SQL Server 2012

Я создал сценарий с помощью sql server 2012, значит, сервер sQL дал мне неправильный сценарий?

5 ответов

Не уверен, правильно ли я понял, что вы пытаетесь сделать, скорее всего, Table1 упоминается как FK в другой таблице.

Если вы это сделаете:

Это даст вам все таблицы, в которых первичный ключ Table1 является FK.

Удаление ограничений, существующих внутри таблицы, не является необходимым шагом для удаления самой таблицы. Удаление всех возможных FK, ссылка на которые есть «Table1».

Что касается второй части вашего вопроса, автоматические сценарии SQL Server во многих отношениях слепы. Скорее всего, таблица, которая мешает вам удалить Table1, опускается ниже или вообще не изменяется сценарием. У RedGate есть несколько инструментов, которые помогают с этими каскадными удалениями (обычно, когда вы пытаетесь удалить кучу таблиц), но он не пуленепробиваемый и довольно дорогой. http://www.red-gate.com/products/sql- разработка / sql-toolbelt /

Во-первых, вам нужно сбросить свой FK.

Я могу порекомендовать вам взглянуть на этот пост о переполнении стека, это очень интересно. Это называется: ограничение внешнего ключа SQL DROP TABLE

Есть хорошее объяснение того, как это сделать.

. Не отбрасывает вашу таблицу, если на нее действительно ссылаются внешние ключи.

Чтобы получить все отношения внешнего ключа, ссылающиеся на вашу таблицу, вы можете использовать этот SQL (если вы используете SQL Server 2005 и выше):

Вам нужно удалить FK в таблице, в которую он был добавлен, теперь это может быть таблица 2, таблица 3 или любая другая таблица, которая ссылается на столбец Table1 как внешний ключ. Затем вы можете удалить Table1.

Вы можете использовать эти запросы, чтобы найти все FK в своей таблице и найти FK в таблицах, в которых используется ваша таблица.

Это происходит потому, что таблица, которую вы пытаетесь изменить, имеет первичный ключ (PK), который упоминается как внешний ключ (FK) где-то в другой таблице. Чтобы узнать, что это за таблица, выполните следующую хранимую процедуру:

EXEC sp_fkeys ‘Table_Name’

А затем запустите команду drop, которая выглядит следующим образом:

DROP TABLE Table_Name

Примечание: ‘dbo.’ является схемой, производной системой по умолчанию, поэтому вам не нужно указывать это в следующей команде. Не беспокойтесь, даже если вы укажете схему, она будет работать.

Не удалось удалить объект 'dbo.Table1', так как на него ссылается ограничение FOREIGN KEY

Сообщение 3726, уровень 16, состояние 1, строка 2 Не удалось удалить объект ‘dbo.Table1’, так как на него ссылается ограничение FOREIGN KEY.

Использование SQL Server 2012

Я сгенерировал скрипт, используя sql server 2012, поэтому сервер sQL дал мне неверный скрипт?

6 ответов

Не уверен, правильно ли я понял, что вы пытаетесь сделать, скорее всего, Table1 упоминается как FK в другой таблице.

Если вы делаете:

Это даст вам все таблицы, где первичным ключом «Table1» является FK.

Удаление ограничений, которые существуют внутри таблицы, не является необходимым шагом для удаления самой таблицы. Удаляя все возможные FK, которые ссылаются на «Table1».

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *