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

SQL-Ex blog

В ранних версиях SQL Server (до 2017) при удалении несуществующей таблицы (DROP TABLE t1;) вы получали сообщение об ошибке:
Не удалось удалить таблицу «t1″, так как она не существует или отсутствуют разрешения.»

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

IF object_id('t1') IS NOT NULL 
DROP TABLE t1;

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

DROP TABLE IF EXISTS t1;

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

CREATE TABLE t1(id INT IDENTITY PRIMARY KEY); 
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY, 
t1_id INT REFERENCES t1); 
GO 
INSERT INTO T1 DEFAULT VALUES; 
GO 3 -- выполним предыдущий пакет (т.е. вставку) 3 раза

SELECT * FROM t1;

INSERT INTO t2(t1_id) VALUES(1),(2); 
SELECT * FROM t2;

id	t1_id
1	1
2	2

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

DROP TABLE t1 CASCADE;

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

DROP TABLE t1;

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

DROP TABLE t2,t1;

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

DROP TABLE IF EXISTS t2,t1;

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

CREATE TABLE IF NOT EXISTS.

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

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Удаление таблиц (компонент Database Engine)

Вы можете удалить таблицу из базы данных в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

Каждое удаление таблицы следует тщательно планировать. Если на таблицу ссылаются существующие запросы, представления, определяемые пользователем функции, хранимые процедуры или программы, то удаление сделает эти объекты недействительными.

В этом разделе

• Перед началом работыОграниченияБезопасность
• Удаление таблицы с помощью следующих средств:Среда SQL Server Management StudioTransact-SQL

Перед началом

Ограничения

• Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.
• При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
• При удалении таблицы, которая содержит столбец varbinary (max) с атрибутом FILESTREAM, не будут удалены никакие данные, которые хранятся в файловой системе.
• Инструкции DROP TABLE и CREATE TABLE нельзя выполнять для одной таблицы в одном пакете. В противном случае может произойти непредвиденная ошибка.
• Любые представления или хранимые процедуры, которые ссылаются на удаляемую таблицу, необходимо явно удалить или изменить, чтобы убрать ссылку на таблицу.

Безопасность

Разрешения

Необходимо разрешение ALTER на схему, к которой принадлежит эта таблица, разрешение CONTROL для этой таблицы или членство в предопределенной роли базы данных db_ddladmin .

Использование среды SQL Server Management Studio

Удаление таблицы из базы данных

1. В обозревателе объектов выберите таблицу, которую необходимо удалить.
2. Щелкните таблицу правой кнопкой мыши и в контекстном меню выберите Удалить .
3. Появится окно подтверждения удаления. Нажмите кнопку Да.

Примечание При удалении таблицы автоматически удаляются все связи с ней.

Использование Transact-SQL

Удаление таблицы в редакторе запросов

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.
2. На стандартной панели выберите пункт Создать запрос.
3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

DROP TABLE dbo.PurchaseOrderDetail; 

Дополнительные сведения см. в разделе DROP TABLE (Transact-SQL)

Создать скрипт для создания таблиц

Помогите пожалуйста с заданием,нужно написать скрипт для создания таблиц в базы данных Библиотека:
— Жанр_книги (Исторический роман, Фантастика, Детективы, Поэзия…);
— Издательство;
— Книга;
— Читатель (перечень зарегистрированных читателей);
— Выдача_книги_читателю.
Использовать операторы Drop/Create Database, Drop/Create/Alter Table.

Я даже приблизительный скрипт написал,но выдает кучу ошибок

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

USE Библиотека GO DROP TABLE Книга GO DROP TABLE Выдача читателю GO DROP TABLE Читатель GO DROP TABLE Издательство GO DROP TABLE Книга GO CREATE TABLE Жанр_Книги (gnum INT IDENTITY PRIMARY KEY, gname VARCHAR(100) NOT NULL, gkniga INT NOT NULL) GO CREATE TABLE Издательство (inum INT IDENTITY PRIMARY KEY, iname VARCHAR(100)NOT NULL, ikniga INT NOT NULL) GO CREATE TABLE Читатель (rnum INT IDENTITY PRIMARY KEY, rname VARCHAR(100) NOT NULL, rnumber INT) GO CREATE TABLE Выдача_Читателю (vnum INT IDENTITY PRIMARY KEY, vdate datetime NOT NULL, vchatatel INT REFERENCES Читатель) GO CREATE TABLE Книга (knum INT IDENTITY PRIMARY KEY, kname VARCHAR(100) NOT NULL, kavtor VARCHAR(100) NOT NULL, kgenre INT REFERENCES Жанр_Книги, kizdatel INT REFERENCES Издательство, kvudacha INT REFERENCES Выдача_Читателю)

Как удалить таблицу в Microsoft SQL Server на T-SQL

Приветствую Вас на сайте Info-Comp.ru! Сегодня в этой заметке мы рассмотрим процесс удаления таблиц в Microsoft SQL Server. Вы узнаете, как удалить одну таблицу, как удалить несколько таблиц, а также как удаляются временные таблицы в SQL Server. Кроме этого, Вы узнаете, как проверить существование таблицы перед ее удалением.

Инструкция DROP TABLE в T-SQL

DROP TABLE – это инструкция T-SQL, с помощью которой удаляются таблицы в Microsoft SQL Server.

Команда DROP относится к группе операторов определения данных (Data Definition Language – DDL).

DROP TABLE полностью удаляет структуру таблицы и связанные с ней индексы, триггеры, ограничения, разрешения и статистику. Инструкцию нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY, в таких случаях сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу.

DROP TABLE не удаляет функции, хранимые процедуры и представления, которые ссылаются на таблицу, поэтому мы должны проверить все зависимости перед удалением таблицы и внести соответствующие изменения в их определение, чтобы избежать возникновения непредвиденных ситуаций в работе этих функции, процедур и представлений.

DROP TABLE позволяет в одной инструкции удалить как одну таблицу, так и несколько. Временные таблицы удаляются точно так же, как и обычные таблицы с помощью инструкции DROP TABLE.

Синтаксис DROP TABLE

DROP TABLE [IF EXISTS] [database_name].[schema_name].[table_name] [ ,…n ];

• DROP TABLE – команда удаления таблицы;
• IF EXISTS – параметр, который проверяет существование таблицы перед ее удалением (данный параметр можно использовать вSQLServer 2016 и более новых версиях);
• database_name – имя базы данных, в которой расположена таблица. Можно и не указывать, тогда инструкция будет выполняться в контексте текущей базы данных;
• schema_name – имя схемы, которой принадлежит таблица. Можно и не указывать, если таблица создана в схеме по умолчанию (dbo), так как SQL Server автоматически использует эту схему, однако если таблица принадлежит другой схеме, то мы должны указать ее название в процессе удаления таблицы;
• ,…n – говорит о том, что через запятую можно указать несколько таблиц.

Удаление одной таблицы в SQL Server

Чтобы удалить одну таблицу в Microsoft SQL Server, необходимо написать инструкцию DROP TABLE и указать название таблицы.

Для примера давайте создадим таблицу Goods, а затем удалим ее с помощью инструкции DROP TABLE.

Создание таблицы

CREATE TABLE Goods ( ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(100) NOT NULL, Price MONEY NULL ); GO

Удаление таблицы

DROP TABLE Goods;

Удаление нескольких таблиц на T-SQL в SQL Server

Если Вам необходимо удалить несколько таблиц сразу, то совсем необязательно писать инструкцию DROP TABLE для удаления каждой таблицы, достаточно написать одну инструкцию и через запятую указать названия таблиц.

Допустим, что у нас есть две таблицы: Table_1 и Table_2. И нам их необходимо удалить.

Для этого мы можем написать следующую инструкцию.

Создание таблиц

CREATE TABLE Table_1 ( ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(100) NOT NULL, Price MONEY NULL ); GO CREATE TABLE Table_2 ( ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(100) NOT NULL, Price MONEY NULL ); GO

Удаление таблиц

DROP TABLE Table_1, Table_2;

Примечание! Если с помощью одной инструкции DROP TABLE удаляются и ссылающаяся таблица, и таблица, содержащая первичный ключ, то ссылающаяся таблица должна быть указана первой в списке.

Удаление таблицы с проверкой на существование этой таблицы

Начиная с 2016 версии Microsoft SQL Server стало возможно указывать параметр IF EXISTS, который позволяет предварительно проверить существование объекта перед его непосредственным удалением.

Теперь нет необходимости писать различные дополнительные условные конструкции IF, чтобы перед удалением проверить, существует ли таблица или нет, инструкция DROP IF EXISTS сделает эту проверку сама.

Более подробно про эту возможность мы говорили в статье – Инструкция DROP IF EXISTS в языке T-SQL.

DROP TABLE IF EXISTS Goods;

В данном случае таблицы Goods не существует, однако инструкция успешно выполнилась.

Заметка! Начинающим рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL.

Удаление временной таблицы на T-SQL

Временные таблицы мы можем принудительно удалять точно так же, как и обычные таблицы.

Создание временной таблицы

CREATE TABLE #TestTable ( ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(100) NOT NULL, Price MONEY NULL ); GO

Удаление временной таблицы

DROP TABLE #TestTable;

В этом примере мы создали временную таблицу, а затем удалили ее.

Заметка! Много статей на тему языка T-SQL Вы можете найти в разделе сайта – Microsoft SQL Server и язык T-SQL.

На сегодня это все, пока!