SQL DROP TABLE 외래 키 제약 조건

SQL DROP TABLE 외래 키 제약 조건

---

이와 같이 데이터베이스의 모든 테이블을 삭제하려면 외래 키 제약 조건을 처리합니까? 그렇지 않다면 어떻게해야합니까?

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]

---

아니요, 실제로 참조하는 외래 키가 있으면 테이블이 삭제되지 않습니다.

테이블을 참조하는 모든 외래 키 관계를 얻으려면이 SQL을 사용할 수 있습니다 (SQL Server 2005 이상인 경우).

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

그리고이 문장이있는 경우 실제로 FK 관계를 삭제하기 위해 SQL 문을 만들 수 있습니다.

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

---

SQL Server Management Studio 2008 (R2) 이상에서는 마우스 오른쪽 단추로

DB-> 작업-> 스크립트 생성

• 삭제하려는 테이블을 선택하십시오.

• "새 쿼리 창에 저장"을 선택하십시오.

• 고급 버튼을 클릭하십시오.

• 스크립트 DROP 및 CREATE를 스크립트 DROP으로 설정하십시오.

• 스크립트 외래 키를 True로 설정하십시오.

• 확인을 클릭하십시오.

• 다음-> 다음-> 완료를 클릭하십시오.

• 스크립트를 본 후 실행하십시오.

---

"자식"테이블을 먼저 삭제하면 외래 키도 삭제됩니다. "부모"테이블을 먼저 삭제하려고하면 "FOREIGN KEY 제약 조건에 의해 참조되므로 개체 'a'를 삭제할 수 없습니다."가 표시됩니다. 오류.

---

다음은 sp_MSdropconstraints프로 시저를 사용하여 모든 테이블을 올바르게 삭제하는 다른 방법 입니다. 내가 생각할 수있는 가장 짧은 코드 :

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

---

SQL Server 인 경우 테이블을 삭제하기 전에 제약 조건을 삭제해야합니다.

---

@mark_s가 게시 한 것의 약간 더 일반적인 버전으로 인해 도움이되었습니다.

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

테이블 이름을 연결하고 결과를 실행하십시오.

---

FOR XML PATH('')여러 입력 행을 단일 출력 행으로 병합 할 수 있는 연결 트릭을 사용하여 테이블 자체가 뒤 따르는 모든 제약 조건을 삭제하는 또 다른 방법 이 있습니다. SQL 2005 이상에서 작동해야합니다.

EXECUTE 명령은 안전을 위해 주석 처리되었습니다.

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)

---

솔루션을 구현하기위한 완전한 스크립트는 다음과 같습니다.

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.

---

Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:

drop table Diary

---

I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).

---

If you are on a mysql server and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:

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

In this way it doesn't matter in what order you use the table in you query.

If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!

---

If I want to delete all the tables in my database

Then it's a lot easier to drop the entire database:

DROP DATABASE WorkerPensions

---

If you want to DROP a table which has been referenced by other table using the foreign key use

DROP TABLE *table_name* CASCADE CONSTRAINTS;
I think it should work for you.

참고URL : https://stackoverflow.com/questions/1776079/sql-drop-table-foreign-key-constraint