Ograniczenie klucza obcego SQL DROP TABLE

Jeśli chcę usunąć wszystkie tabele z mojej bazy danych w ten sposób, czy zajmie się ograniczeniem klucza obcego? Jeśli nie, jak mam się tym najpierw zająć?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]

Nie, to nie spowoduje usunięcia tabeli, jeśli rzeczywiście istnieją do niej odwołania do obcych kluczy.

Aby uzyskać wszystkie relacje klucza obcego odwołujące się do tabeli, możesz użyć tego języka SQL (jeśli korzystasz z SQL Server 2005 i nowszych):

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

a jeśli istnieją, za pomocą tej instrukcji tutaj można utworzyć instrukcje SQL, które faktycznie usuwają te relacje FK:

SELECT 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

W SQL Server Management Studio 2008 (R2) i nowszych można kliknąć prawym przyciskiem myszy plik

DB -> Zadania -> Generuj skrypty

• Wybierz tabele, które chcesz upuścić.

• Wybierz „Zapisz w nowym oknie zapytania”.

• Kliknij przycisk Zaawansowane.

• Ustaw Script DROP i CREATE na Script DROP.

• Ustaw klucze obce skryptu na True.

• Kliknij OK.

• Kliknij Dalej -> Dalej -> Zakończ.

• Wyświetl skrypt, a następnie wykonaj.

Jeśli najpierw usuniesz tabelę podrzędną, klucz obcy również zostanie usunięty. Jeśli spróbujesz najpierw usunąć tabelę „nadrzędną”, otrzymasz komunikat „Nie można usunąć obiektu 'a', ponieważ odwołuje się do niego ograniczenie KLUCZ OBCY”. błąd.

Oto inny sposób na poprawne usunięcie wszystkich tabel przy użyciu sp_MSdropconstraintsprocedury. Najkrótszy kod, jaki mogłem wymyślić:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";

Jeśli jest to SQL Server, musisz usunąć ograniczenie, zanim będzie można usunąć tabelę.

Nieco bardziej ogólna wersja tego, co opublikował @mark_s, pomogło mi

SELECT 
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(k.parent_object_id) +
'.[' + OBJECT_NAME(k.parent_object_id) + 
'] DROP CONSTRAINT ' + k.name
FROM sys.foreign_keys k
WHERE referenced_object_id = object_id('your table')

po prostu podłącz nazwę swojej tabeli i wykonaj jej wynik.

Oto inny sposób usunięcia wszystkich ograniczeń, po których następują same tabele, przy użyciu sztuczki konkatenacji polegającej FOR XML PATH('')na połączeniu wielu wierszy wejściowych w jeden wiersz wyjściowy. Powinien działać na czymkolwiek SQL 2005 i nowszym.

Zostawiłem polecenia EXECUTE zakomentowane ze względów bezpieczeństwa.

DECLARE @SQL NVARCHAR(max)
;WITH fkeys AS (
    SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname 
    FROM sys.foreign_keys fk
    JOIN sys.objects o ON fk.parent_object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
)
SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
FROM fkeys
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

Oto kompletny skrypt do wdrożenia rozwiązania:

create Procedure [dev].DeleteTablesFromSchema
(
    @schemaName varchar(500)
)
As 
begin
    declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128),  @constraintName nvarchar(128)
    declare @sql nvarchar(max)
    -- delete FK first
    declare cur1 cursor for
    select distinct 
    CASE WHEN t2.[object_id] is NOT NULL  THEN  s2.name ELSE s.name END as SchemaName,
    CASE WHEN t2.[object_id] is NOT NULL  THEN  t2.name ELSE t.name END as TableName,
    CASE WHEN t2.[object_id] is NOT NULL  THEN  OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
    from sys.objects t 
        inner join sys.schemas s 
            on t.[schema_id] = s.[schema_id]
        left join sys.foreign_key_columns d 
            on  d.parent_object_id = t.[object_id]
        left join sys.foreign_key_columns d2 
            on  d2.referenced_object_id = t.[object_id]
        inner join sys.objects t2 
            on  d2.parent_object_id = t2.[object_id]
        inner join sys.schemas s2 
            on  t2.[schema_id] = s2.[schema_id]
    WHERE t.[type]='U' 
        AND t2.[type]='U'
        AND t.is_ms_shipped = 0 
        AND t2.is_ms_shipped = 0 
        AND s.Name=@schemaName
    open cur1
    fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
    while @@fetch_status = 0
    BEGIN
        set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
        exec(@sql)
        fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
    END
    close cur1
    deallocate cur1

    DECLARE @tableName nvarchar(128)
    declare cur2 cursor for
    select s.Name, p.Name
    from sys.objects p
        INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE p.[type]='U' and is_ms_shipped = 0 
    AND s.Name=@schemaName
    ORDER BY s.Name, p.Name
    open cur2

    fetch next from cur2 into @schemaName,@tableName
    while @@fetch_status = 0
    begin
        set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
        exec(@sql)
        fetch next from cur2 into @schemaName,@tableName
    end

    close cur2
    deallocate cur2

end
go

Removing Referenced FOREIGN KEY Constraints
Assuming there is a parent and child table Relationship in SQL Server:

--First find the name of the Foreign Key Constraint:
  SELECT * 
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('States')

--Then Find foreign keys referencing to dbo.Parent(States) table:
   SELECT name AS 'Foreign Key Constraint Name', 
           OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
   FROM sys.foreign_keys 
   WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
              OBJECT_NAME(referenced_object_id) = 'dbo.State'

 -- Drop the foreign key constraint by its name 
   ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

 -- You can also use the following T-SQL script to automatically find 
 --and drop all foreign key constraints referencing to the specified parent 
 -- table:

 BEGIN

DECLARE @stmt VARCHAR(300);

-- Cursor to generate ALTER TABLE DROP CONSTRAINT statements  
 DECLARE cur CURSOR FOR
 SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + 
 OBJECT_NAME(parent_object_id) +
                ' DROP CONSTRAINT ' + name
 FROM sys.foreign_keys 
 WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
            OBJECT_NAME(referenced_object_id) = 'states';

 OPEN cur;
 FETCH cur INTO @stmt;

 -- Drop each found foreign key constraint 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC (@stmt);
    FETCH cur INTO @stmt;
  END

  CLOSE cur;
  DEALLOCATE cur;

  END
  GO

--Now you can drop the parent table:

 DROP TABLE states;
--# Command(s) completed successfully.

Za pomocą programu SQL Server Manager można usunąć ograniczenia klucza obcego z interfejsu użytkownika. Jeśli chcesz usunąć tabelę, Diaryale tabela User ma klucz obcy DiaryIdwskazujący na Diarytabelę, możesz rozwinąć tabelę (używając symbolu plusa), Usera następnie rozwinąć Foreign Keyssekcję. Kliknij prawym przyciskiem myszy klucz obcy wskazujący na tabelę dziennika, a następnie wybierz Delete. Następnie możesz rozwinąćColumns sekcję, kliknąć prawym przyciskiem myszy i usunąć również kolumnę DiaryId. Następnie możesz po prostu biegać:

drop table Diary

Wiem, że Twoje pytanie dotyczy usunięcia wszystkich tabel, więc może to nie być przydatne w tym przypadku. Jeśli jednak chcesz tylko usunąć kilka tabel, uważam, że jest to przydatne (tytuł nie wspomina wyraźnie o usunięciu wszystkich tabel).

Jeśli jesteś na serwerze mysql i nie masz nic przeciwko utracie tabel, możesz użyć prostego zapytania, aby usunąć wiele tabel naraz:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;

W ten sposób nie ma znaczenia, w jakiej kolejności używasz tabeli w zapytaniu.

Jeśli ktoś ma zamiar coś powiedzieć o tym, że nie jest to dobre rozwiązanie, jeśli masz bazę danych z wieloma tabelami: Zgadzam się!

wykonaj poniższy kod, aby uzyskać nazwę ograniczenia klucza obcego, które blokuje upuszczanie. Na przykład biorę rolesstół.

      SELECT *
      FROM sys.foreign_keys
      WHERE referenced_object_id = object_id('roles');

      SELECT name AS 'Foreign Key Constraint Name',
      OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id)
      AS 'Child Table' FROM sys.foreign_keys
      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo'
      AND OBJECT_NAME(referenced_object_id) = 'dbo.roles'

otrzymasz nazwę FK, jak poniżej: FK__Table1__roleId__1X1H55C1

teraz uruchom poniższy kod, aby usunąć odwołanie FK otrzymane z góry.

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

Gotowe!

Jeśli chcę usunąć wszystkie tabele w mojej bazie danych

Wtedy dużo łatwiej jest usunąć całą bazę danych:

DROP DATABASE WorkerPensions

Jeśli chcesz skorzystać z DROPtabeli, do której odwołuje się inna tabela, używając klucza obcego

DROP TABLE *table_name* CASCADE CONSTRAINTS;
Myślę, że to powinno działać dla Ciebie.