Ograniczenie klucza obcego SQL DROP TABLE


154

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]


2
Sprawdź tę odpowiedź, pomogło mi;) stackoverflow.com/a/5488670/2205144
xatz

Odpowiedzi:


335

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')

Uwaga: wygenerowana tabela zawiera instrukcje usuwania poszczególnych ograniczeń do uruchomienia w innym zapytaniu. W moim przypadku zdziałało cuda, ponieważ musiałem pozbyć się bezużytecznej tabeli (do celów testowych), jednocześnie utrzymując pozostałe tabele, które miały nienaruszone FK.
Mauricio Quintana

1
Musiałem użyć parent_object_id zamiast referenced_object_id
Tom Robinson

2
Alternatywą dla instrukcji SELECT w celu pobrania wszystkich tabel odwołań jest: EXEC sp_fkeys 'Student';
Buggieboy

mała korekta tego zapytania SELECT 'ALTER TABLE' + OBJECT_SCHEMA_NAME (parent_object_id) + '. [' + OBJECT_NAME (parent_object_id) + '] DROP CONSTRAINT [nazwa]' FROM sys.foreign_keys WHERE referenced_object_id = object_id ('Student') replace [name] z nazwą
CONSTRAINT

1
Jak przypisać ostatnią selekcję jako zmienną i wykonać ją?
Hrvoje T

44

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.


12
nadal to samo „Nie można usunąć obiektu„ moja_tabela ”, ponieważ odwołuje się do niego ograniczenie FOREIGN KEY.
FrenkyB

6
Jak ta odpowiedź ma ocenę 28 + jest poza mną ... nadal nie działa kolego
AltF4_

1
Chociaż generuje skrypt - ten skrypt nie rozwiązuje problemu, który polega na usunięciu tabeli, do której odwołują się ograniczenia klucza obcego.
Alexey Shevelyov

U mnie zadziałało jak urok.
Johan

21

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.


2
To prawda, ale nie ma rozwiązania. Dziecko może mieć obce klucze do innych tabel i / lub możesz nie chcieć ich upuszczać.
MaR

4
To prawda, i rozwiązaniem, jeśli celem jest, jak wspomniano, „Jeśli chcę usunąć wszystkie tabele z mojej bazy danych ...”
Philip Kelley

4
jest to znacznie lepsza odpowiedź niż zaakceptowana, biorąc pod uwagę, że pytający chce „usunąć wszystkie tabele z mojej bazy danych”
lukkea

17

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 ?";

Niesamowity człowiek ............
edCoder

2

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


2

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.


1
Cześć, próbowałem usunąć moje ograniczenia klucza obcego zgodnie z powyższym, ale kiedy idę później porzucić tabelę, ciągle mówi: „Nie można upuścić obiektu, ponieważ nadal odwołuje się do niego ograniczenie klucza obcego ... jakieś pomysły? Z góry dziękuję.
daniness

1

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)

To nie działa dobrze, ponieważ nie wszystkie FK zostaną usunięte (tylko te, w których nasze tabele są używane jako nadrzędne, gdzie powinniśmy usunąć również ograniczenie, w którym nasza tabela została użyta jako „przywoływana”).
Roman Pokrovskij

Roman, oryginalny post dotyczy usuwania ograniczeń klucza obcego. Moja odpowiedź nie ma na celu niczego poza tym.
Warren Rumak

1

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

1
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.

0

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).


0

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ę!


DostajęIncorrect syntax near '='. (102) (SQLExecDirectW)
Matt

@Matt Bit trudno odgadnąć, przy którym '=' pojawia się ten błąd.
Els den Iep

3
foreign_key_checksnie będzie działać na serwerze MSSQL. Myślę, że to zmienna specyficzna dla MySql.
ooXei1sh,

1
@ ooXei1sh Nie, nie w MSSQL. Dlatego na początku mojego wpisu mówię: „Jeśli jesteś na serwerze MYSQL”.
Els den Iep

0

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!


-4

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

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

DROP DATABASE WorkerPensions

71
Dając ci -1 za to, ponieważ nie jest to poprawna odpowiedź na pytanie, z dwóch ważnych powodów: 1) Usuwa o wiele więcej niż tabele! Procedury składowane, funkcje, UDT, zabezpieczenia, zespoły .NET itp. Znikają z DROP DATABASE. 2) Możesz nie mieć uprawnień do tworzenia baz danych, np. Centralnie zarządzanego środowiska deweloperskiego, w którym bazy danych są dostarczane przez dział IT i masz dodatkowe wymagania podczas tworzenia, o których nie jesteś świadomy.
Warren Rumak

-4

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.


1
nie ma cascade constraintsna serwerze sql
JackyJohnson
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.