programing

SQL DROP TABLE 외부 키 제약 조건

cafebook 2023. 4. 13. 21:10
반응형

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

또한 이 문장에서 SQL 문을 생성하여 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')

SQL Server Management Studio 2008(R2) 이후 버전에서는

DB -> 태스크 -> 스크립트 생성

  • 드롭할 테이블을 선택합니다.

  • "Save to new query 창"을 선택합니다.

  • [상세설정] 버튼을 클릭합니다.

  • Script DROP 및 CREATE를 Script DROP으로 설정합니다.

  • 스크립트 외부 키를 True로 설정합니다.

  • [확인] 을 클릭합니다.

  • [다음] - > [다음] - > [종료] 를 누릅니다.

  • 스크립트를 보고 실행하세요.

먼저 "자녀" 테이블을 삭제하면 외부 키도 삭제됩니다.먼저 "부모" 테이블을 삭제하려고 하면 "Could not drop object 'a' is reference by a FORENAL KEY contains." 오류가 나타납니다.

또 다른 할 수 .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는 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.

모든 것이 훨씬 간단하다.체크를 끄고 켜는 구성이 있습니다.

예를 들어 MySQL을 사용하는 경우 이 기능을 끄려면 SET foreign_key_module = 0으로 입력해야 합니다.

그런 다음 테이블을 삭제 또는 클리어하고 SET foreign_key_disples = 1 체크박스를 다시 활성화합니다.

SQL Server Manager를 사용하여 UI에서 외부 키 제약 조건을 삭제할 수 있습니다.테이블을 삭제하려면Diary그러나 사용자 테이블에는 외부 키가 있습니다.DiaryId가리키다Diary테이블은 (+ 기호를 사용하여) 전개할 수 있습니다.User테이블을 열고 나서Foreign Keys부분.다이어리 테이블을 가리키는 외부 키를 마우스 오른쪽 버튼으로 클릭한 다음Delete. 그런 다음 확장 가능합니다.Columns섹션, 마우스 오른쪽 버튼을 클릭하여 열을 삭제합니다.DiaryId그럼 그냥 도망가면 돼

drop table Diary

실제 질문은 모든 테이블을 삭제하는 것이므로 이 경우 유용하지 않을 수 있습니다.단, 몇 개의 테이블만 삭제하는 경우에는 편리하다고 생각합니다(제목에는 모든 테이블의 삭제가 명시적으로 기재되어 있지 않습니다).

다음 코드를 실행하여 드롭을 차단하는 외부 키 제약 이름을 가져옵니다.예를 들어, 저는 이 명령어를roles테이블.

      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'

다음과 같은 FK 이름을 얻을 수 있습니다.FK__Table1__roleId__1X1H55C1

이제 아래 코드를 실행하여 위에서 얻은 FK 참조를 제거합니다.

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

알았어!

mysql 서버(MSQL이 아님)에 있고 테이블이 손실되어도 문제가 없는 경우 간단한 쿼리를 사용하여 여러 테이블을 한 번에 삭제할 수 있습니다.

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

이 방법에서는 질의에서 테이블을 사용하는 순서가 문제가 되지 않습니다.

테이블이 많은 데이터베이스가 있는 경우 이 솔루션이 적합하지 않다고 말하는 사람이 있다면 다음과 같이 하십시오.동감이에요!

모든 외부 키 찾기..대본을 쓰다

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

그런 다음 하위 테이블에서 외부 키를 삭제합니다.이제 부모 테이블을 드롭할 수 있습니다.

상위 테이블을 다시 작성하려면 이전에 작성한 스크립트를 실행해야 합니다.

이 스크립트를 사용하면 제약 조건 이름의 문자열 일치를 사용하여 모든 외부 키 억제를 삭제할 수 있습니다.

    DECLARE @constraintMatchString nvarchar(max) = N'FK_<SOME-MATCH-STRING>%';
    DECLARE @sql nvarchar(max) = N'';

    ;WITH x (schemaAndTableName, constraintName) AS 
    (
      SELECT 
        schemaAndTableName = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)),
        constraintName = name
      FROM sys.foreign_keys
      where name like @constraintMatchString
      group by QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)), name
    )
    SELECT @sql += N'ALTER TABLE ' + schemaAndTableName + N' DROP CONSTRAINT [' +constraintName +N'];
    ' FROM x;

    EXEC sys.sp_executesql @sql;

를 교환해 주세요.FK_<SOME-MATCH-STRING>%필요에 따라서 값을 매겨 주세요(주:%와일드카드 일치)

원하신다면DROP외래 키를 사용하여 다른 표에서 참조된 표

DROP TABLE *table_name* CASCADE CONSTRAINTS;
너한테 효과가 있을 것 같아.

데이터베이스의 모든 테이블을 삭제하려면

그러면 전체 데이터베이스를 삭제하는 것이 훨씬 쉬워집니다.

DROP DATABASE WorkerPensions

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

반응형

'programing' 카테고리의 다른 글

Excel 날짜에서 Unix 타임스탬프까지  (0) 2023.04.13
왜 "while(!feof(file)"은 항상 잘못된 것일까요?  (0) 2023.04.13
여러 개의 목록 보기를 뷰 모델에 바인딩하려면 어떻게 해야 합니까?  (0) 2023.04.13
NSAttributedString의 boundingRectWithSize가 잘못된 크기를 반환함  (0) 2023.04.13
UIScroll View 콘텐츠란부동산 삽입 대상?  (0) 2023.04.13

'programing'의 다른글

  • 현재글SQL DROP TABLE 외부 키 제약 조건

관련글

댓글

티스토리툴바