반응형
외부 키 제약 조건이 테이블을 참조하고 있으므로 테이블을 잘라낼 수 없습니까?
MSSQL2005를 사용하여 자녀 테이블(FK 관계의 프라이머리 키가 있는 테이블)을 처음 잘라낸 경우 외부 키 제약으로 테이블을 잘라낼 수 있습니까?
내가 할 수 있다는 거 알아
- 사용하다
DELETE을 붙이지 않고 where를 붙입니다.RESEED - FK를 제거하고 테이블을 잘라낸 다음 FK를 다시 만듭니다.
부모보다 먼저 자식 테이블을 잘라내기만 하면 위의 옵션 중 하나를 실행하지 않아도 된다고 생각했는데 다음 오류가 나타납니다.
'TableName' 테이블은 FORENAL KEY 제약 조건이 참조하고 있으므로 잘라낼 수 없습니다.
DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME', RESEED, 0)
속도가 매우 느리기 때문에 수백만 개 이상의 레코드가 있는 경우 이 방법은 필요하지 않을 수 있습니다.
정답. FK 제약 조건이 있는 테이블은 잘라낼 수 없습니다.
일반적으로 이를 위한 프로세스는 다음과 같습니다.
- 제약 해제
- 테이블을 잘라냅니다.
- 구속조건을 다시 만듭니다.
(물론 모든 것은 거래에서 이루어집니다.
물론 이것은 아이가 이미 잘린 경우에만 적용됩니다.그렇지 않으면 데이터가 어떻게 보이는지에 따라 다른 경로를 사용합니다(여기에는 변수가 너무 많습니다).
원본 포스터에서 이러한 이유를 확인했습니다. 자세한 내용은 이 답변을 참조하십시오.
★★★★★★★★★★★★★★★★★★TRUNCATE TABLE는 DDL 명령어입니다.테이블 내의 레코드가 자녀 테이블의 레코드에 의해 참조되고 있는지 여부를 확인할 수 없습니다.
★★★★★DELETETRUNCATE TABLE는 데이터베이스가 다른 레코드에 의해 참조되지 않도록 할 수 있기 때문입니다.
이 - 아니, 아니에요! - 왜요?ALTER TABLE
-- Delete all records
DELETE FROM [TableName]
-- Set current ID to "1"
-- If table already contains data, use "0"
-- If table is empty and never insert data, use "1"
-- Use SP https://github.com/reduardo7/TableTruncate
DBCC CHECKIDENT ([TableName], RESEED, 0)
저장 프로시저로서
https://github.com/reduardo7/TableTruncate
속도가 매우 느리기 때문에 수백만 개 이상의 레코드가 있는 경우 이 방법은 필요하지 않을 수 있습니다.
위에서 설명한 솔루션 @denver_citizen은 효과가 없었지만 그 정신이 마음에 들어 몇 가지를 수정했습니다.
- 저장 프로시저로 만들었습니다.
- 외부 키를 채우고 다시 생성하는 방법을 변경했습니다.
- 원래 스크립트는 참조된 테이블을 모두 잘라냅니다.이 때문에 참조된 테이블에 다른 외부 키 참조가 있는 경우 외부 키 위반 오류가 발생할 수 있습니다.이 스크립트는 파라미터로 지정된 테이블만 잘라냅니다.올바른 순서로 모든 테이블에서 이 저장 프로시저를 여러 번 호출하는 것은 사용자에게 달려 있습니다.
일반인을 위해 업데이트된 스크립트는 다음과 같습니다.
CREATE PROCEDURE [dbo].[truncate_non_empty_table]
@TableToTruncate VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON
-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- Drop Temporary tables
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
DROP TABLE #FKs
-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
ORDER BY OBJECT_NAME(parent_object_id)
-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'
-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'
-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'
-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'
IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'
-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '4. Truncating Tables...'
-- TRUNCATE TABLES
-- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys
-- to resolve this the stored procedure should be called recursively, but I dont have the time to do it...
/*
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
*/
IF @Verbose = 1
PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']'
IF @Debug = 1
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'
-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '6. Process Completed'
END
delete 문을 사용하여 테이블 내의 모든 행을 삭제한 후 다음 명령을 사용합니다.
delete from tablename
DBCC CHECKIDENT ('tablename', RESEED, 0)
편집: SQL Server 구문 수정
제가 사용한 매우 간단한 솔루션의 예를 찾을 수 없었기 때문에, 다음과 같습니다.
- 외부 키 드롭;
- 테이블 잘라내기
- 외부 키 재생성
여기 있습니다.
1) 고장(예: FK_ 이유: FK_ 이유: FK_B 인 f 를 ( 름 이 드 다: find 습 니 을 ( 1 example) thereasonID, 테이블 이에서TABLE_OWNING_CONSTRAINT) 2) Remove that key from the table:2) 테이블에서 해당 키를 제거합니다.
ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON
3) 원하는 테이블을 잘라냅니다.
TRUNCATE TABLE TABLE_TO_TRUNCATE
4) 첫 번째 표에 키를 다시 추가합니다.
ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID)
바로 그겁니다.
프로세스는 외부 키 제약 조건을 제거하고 테이블을 잘라낸 다음 다음 단계에 따라 제약을 추가합니다.
이것은 MySQL 전용입니다.
SET FOREIGN_KEY_CHECKS = 0;
truncate table "yourTableName";
SET FOREIGN_KEY_CHECKS = 1;
you can follow this step, By 다음 단계를 수행할 수 있습니다.reseeding table테이블의 데이터를 삭제할 수 있습니다.테이블의 데이터를 삭제할 수 있습니다.
delete from table_name
dbcc checkident('table_name',reseed,0)
에러가 발생하면 프라이머리 테이블을 다시 설치해야 합니다.
여기 프로세스를 자동화하기 위해 작성한 스크립트가 있습니다.도움이 됐으면 좋겠어요.
SET NOCOUNT ON
-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)
DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)
-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1
SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
-- Drop Temporary tables
DROP TABLE #FKs
-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
ORDER BY OBJECT_NAME(parent_object_id)
-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'
-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'
-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'
-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'
IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'
-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '4. Truncating Tables...'
-- TRUNCATE TABLES
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'
-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'
END
IF @Verbose = 1
PRINT '6. Process Completed'
@denver_citizen과 @Peter Ssanto의 답변은 별로 효과가 없었지만, 다음과 같이 수정했습니다.
- 복합 키
- 삭제 시 및 업데이트 시 작업
- 재추가 시 인덱스 확인
- dbo 이외의 스키마
- 여러 테이블을 동시에 사용 가능
DECLARE @Debug bit = 0;
-- List of tables to truncate
select
SchemaName, Name
into #tables
from (values
('schema', 'table')
,('schema2', 'table2')
) as X(SchemaName, Name)
BEGIN TRANSACTION TruncateTrans;
with foreignKeys AS (
SELECT
SCHEMA_NAME(fk.schema_id) as SchemaName
,fk.Name as ConstraintName
,OBJECT_NAME(fk.parent_object_id) as TableName
,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName
,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
,fc.constraint_column_id
,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
,fk.delete_referential_action_desc
,fk.update_referential_action_desc
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fc
ON fk.object_id = fc.constraint_object_id
JOIN #tables tbl
ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name
JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name
and SCHEMA_NAME(t.schema_id) = tbl.SchemaName
and t.OBJECT_ID = fc.referenced_object_id
)
select
quotename(fk.ConstraintName) AS ConstraintName
,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
,STUFF((
SELECT ',' + quotename(fk2.ColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ColumnNames
,STUFF((
SELECT ',' + quotename(fk2.ReferencedColumnName)
FROM foreignKeys fk2
WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
ORDER BY fk2.constraint_column_id
FOR XML PATH('')
),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc
-- Drop FKs
select
identity(int,1,1) as ID,
'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk
-- Truncate
insert into #scripts
select distinct
'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script
from #tables tbl
-- Recreate
insert into #scripts
select
'ALTER TABLE ' + fk.TableName +
' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName +
' FOREIGN KEY ('+ fk.ColumnNames +')' +
' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script
from #FKs fk
DECLARE @script nvarchar(MAX);
DECLARE curScripts CURSOR FOR
select script
from #scripts
order by ID
OPEN curScripts
WHILE 1=1 BEGIN
FETCH NEXT FROM curScripts INTO @script
IF @@FETCH_STATUS != 0 BREAK;
print @script;
IF @Debug = 0
EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts
drop table #scripts
drop table #FKs
drop table #tables
COMMIT TRANSACTION TruncateTrans;
구속조건을 삭제하지 않으면 테이블을 잘라낼 수 없습니다.비활성화도 작동하지 않습니다.모든 것을 버려야 해 모든 제약을 제거하고 다시 만드는 대본을 만들었어
반드시 트랜잭션으로 포장해 주세요.
SET NOCOUNT ON
GO
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--DROP CONSTRAINT:
DECLARE @dynSQL varchar(MAX);
DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table
OPEN cur
FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
truncate table your_table
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
--HERE GOES YOUR TRUNCATES!!!!!
---------------------
--ADD CONSTRAINT:
DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table
OPEN cur2
FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0
BEGIN
exec(@dynSQL)
print @dynSQL
FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2
제가 제대로 이해했다면, 당신이 원하는 것은 통합 테스트를 포함한 DB를 위한 깨끗한 환경을 구축하는 것입니다.
여기서의 접근법은 스키마 전체를 삭제하고 나중에 다시 만드는 것입니다.
이유:
- 스키마 작성 스크립트가 이미 있을 수 있습니다.테스트 격리에 재사용하는 것은 간단합니다.
- 스키마 작성은 매우 빠릅니다.
- 이 접근방식을 사용하면 각 설비가 (일시적인 이름을 사용하여) 새로운 스키마를 작성하도록 스크립트를 쉽게 셋업할 수 있습니다.그 후 테스트 혼합을 병행하여 실행할 수 있기 때문에 테스트 스위트의 가장 느린 부분을 훨씬 빠르게 실행할 수 있습니다.
웹상의 다른 곳에 있습니다.
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
-- EXEC sp_MSForEachTable 'DELETE FROM ?' -- Uncomment to execute
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
다음 방법을 작성하여 파라미터화하려고 했습니다.그러면 다음 방법으로 실행할 수 있습니다.Query document또는 쉽게 유용하게 사용할 수 있습니다.
A) 삭제
테이블에 수백만 개의 레코드가 없는 경우 이 기능은 정상적으로 작동하며 Alter 명령어가 없습니다.
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb' --< Db Name
DECLARE @Schema AS NVARCHAR(30) = 'dbo' --< Schema
DECLARE @TableName AS NVARCHAR(30) = 'Book' --< Table Name
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName
EXECUTE sp_executesql @Query
SET @Query=@DbName+'.'+@Schema+'.'+@TableName
DBCC CHECKIDENT (@Query,RESEED, 0)
- 상기 답변에서는 질문에서 언급된 문제를 해결하는 방법은 @s15199d의 답변에 기초하고 있습니다.
B) 잘라내기
테이블에 수백만 개의 레코드가 있거나 코드의 Alter 명령에 문제가 없는 경우 다음 명령을 사용하십시오.
-- Book Student
--
-- | BookId | Field1 | | StudentId | BookId |
-- --------------------- ------------------------
-- | 1 | A | | 2 | 1 |
-- | 2 | B | | 1 | 1 |
-- | 3 | C | | 2 | 3 |
---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'
DECLARE @Schema AS NVARCHAR(30) = 'dbo'
DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book'
DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint
DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint' --< Decelations About FK_Book_Constraint
------------------ /Just Fill Parameters Value ----------------
DECLARE @Query AS NVARCHAR(2000)
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName
EXECUTE sp_executesql @Query
SET @Query= 'Truncate Table '+ @TableName_ToTruncate
EXECUTE sp_executesql @Query
SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')'
EXECUTE sp_executesql @Query
위의 답변에서 질문에서 언급된 문제를 해결하는 방법은 @LauroWolffValenteSobrinhu의 답변에 기초하고 있습니다.
여러 개의 구속조건이 있는 경우 위와 같은 코드를 위의 쿼리에 추가해야 합니다.
또, 상기의 코드 베이스 @SerjSagan answer를 변경해, 제약 조건을 유효하게 할 수도 있습니다.
잘라내는 것은 효과가 없었습니다.삭제 + 다시 붙이는 것이 가장 좋은 방법입니다.삭제 및 재설치를 수행하기 위해 엄청난 수의 테이블을 반복해야 하는 경우 ID 열이 없는 테이블에 문제가 발생할 수 있습니다.다음 코드는 재설치를 시도하기 전에 ID 열이 존재하는지 여부를 확인합니다.
EXEC ('DELETE FROM [schemaName].[tableName]')
IF EXISTS (Select * from sys.identity_columns where object_name(object_id) = 'tableName')
BEGIN
EXEC ('DBCC CHECKIDENT ([schemaName.tableName], RESEED, 0)')
END
★★★의 MS SQL적어도 새로운 버전에서는, 다음과 같은 코드로 구속을 무효로 할 수 있습니다.
ALTER TABLE Orders
NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
TRUNCATE TABLE Customers
GO
ALTER TABLE Orders
WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO
유일한 방법은 잘라내기 전에 외부 키를 삭제하는 것입니다.그리고 데이터를 잘라낸 후에는 인덱스를 다시 생성해야 합니다.
다음 스크립트는 모든 외부 키 제약 조건을 삭제하기 위해 필요한 SQL을 생성합니다.
DECLARE @drop NVARCHAR(MAX) = N'';
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
SELECT @drop
다음으로 다음 스크립트는 외부 키를 재생성하기 위해 필요한 SQL을 생성합니다.
DECLARE @create NVARCHAR(MAX) = N'';
SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
SELECT @create
생성된 스크립트를 실행하여 모든 외부 키를 삭제하고 테이블을 잘라낸 다음 생성된 스크립트를 실행하여 모든 외부 키를 다시 만듭니다.
여기서부터 쿼리를 받습니다.
이 문제에 대한 저의 해결책입니다.PK를 변경하기 위해 사용했지만 생각은 같습니다.도움이 되었으면 합니다.)
PRINT 'Script starts'
DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)
-->> Type the primary table name
SET @TablN = ''
--------------------------------------------------------------------------------------- ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
into #ConTab
from sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type = 'F' and p.name = @TablN
ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/
PRINT 'Cursor 1 starting. All refernce FK will be droped'
SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------
PRINT 'Altering primary table begin'
TRUNCATE TABLE table_name
PRINT 'Altering finished'
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------
PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR = CURSOR SCROLL
FOR
select foreign_key_name
, keycnt
, foreign_table
, foreign_column_1
, foreign_column_2
, primary_table
, primary_column_1
, primary_column_2
from #ConTab
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')
EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')
FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2,
@primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'
다음은 FK 제약 조건에서도 사용할 수 있으며, 다음 답변을 조합하여 지정된 테이블만 삭제합니다.
USE [YourDB];
DECLARE @TransactionName varchar(20) = 'stopdropandroll';
BEGIN TRAN @TransactionName;
set xact_abort on; /* automatic rollback https://stackoverflow.com/a/1749788/1037948 */
-- ===== DO WORK // =====
-- dynamic sql placeholder
DECLARE @SQL varchar(300);
-- LOOP: https://stackoverflow.com/a/10031803/1037948
-- list of things to loop
DECLARE @delim char = ';';
DECLARE @foreach varchar(MAX) = 'Table;Names;Separated;By;Delimiter' + @delim + 'AnotherName' + @delim + 'Still Another';
DECLARE @token varchar(MAX);
WHILE len(@foreach) > 0
BEGIN
-- set current loop token
SET @token = left(@foreach, charindex(@delim, @foreach+@delim)-1)
-- ======= DO WORK // ===========
-- dynamic sql (parentheses are required): https://stackoverflow.com/a/989111/1037948
SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- https://stackoverflow.com/a/11784890
PRINT @SQL;
EXEC (@SQL);
-- ======= // END WORK ===========
-- continue loop, chopping off token
SET @foreach = stuff(@foreach, 1, charindex(@delim, @foreach+@delim), '')
END
-- ===== // END WORK =====
-- review and commit
SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged;
COMMIT TRAN @TransactionName;
주의:
삭제할 순서대로 테이블을 선언하는 것이 여전히 도움이 된다고 생각합니다(즉, 먼저 종속성을 삭제합니다).이 답변에서 알 수 있듯이 루프 고유의 이름이 아닌 모든 테이블을 다음 이름으로 대체할 수 있습니다.
EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);';
이 답변 중 어느 것도 내 경우와 같은 것이 되지 않는 경우는, 다음과 같이 해 주세요.
- 제약 조건의 폐기
- null을 허용하도록 모든 값 설정
- 테이블 잘라내기
- 삭제된 제약 조건을 추가합니다.
행운을 빕니다.
삭제 후 자동 증분 재설정:
delete from tablename;
그리고나서
ALTER TABLE tablename AUTO_INCREMENT = 1;
SSMS에서 키를 나타내는 다이어그램을 열었습니다.키를 삭제하고 파일을 잘라낸 후 새로 고친 후 다이어그램에 다시 초점을 맞추어 [Identity]박스를 클리어한 후 복원하여 업데이트를 작성했습니다.다이어그램을 저장하면 "작업하는 동안 데이터베이스에서 변경 사항이 수행되었습니다" 대화상자가 아닌 "저장" 대화상자가 나타납니다. 예를 누르면 키가 복원되고 다이어그램의 래치된 복사본에서 키가 복원됩니다.
어떤 빈도로든 스케줄에 관계없이 DML 문장은 절대 사용하지 않습니다.트랜잭션 로그에 쓰는 데 드는 비용이 너무 많이 들어 전체 데이터베이스를 다음과 같이 설정합니다.SIMPLE테이블 하나를 잘라내는 복구 모드는 말도 안 됩니다.
가장 좋은 방법은 불행히도 힘들거나 힘든 방법입니다.그 이유:
- 제약 조건의 폐기
- 테이블 잘라내기
- 제약 조건 재작성
이를 위한 프로세스에는 다음 단계가 포함됩니다.
- SSMS에서 해당 테이블을 마우스 오른쪽 버튼으로 클릭하고 View Dependencies를 선택합니다.
- 참조된 테이블(있는 경우)을 적어 둡니다.
- 오브젝트 탐색기로 돌아가서 [Keys]노드를 펼쳐 외부 키(있는 경우)를 확인합니다.
- 스크립트 작성 시작(드롭/잘라내기/재작성)
이러한 성격의 스크립트는 다음 시간 내에 실행되어야 합니다.begin tran ★★★★★★★★★★★★★★★★★」commit tran
이것은 엔티티 프레임워크를 사용하는 사용자를 위한 예시입니다.
: " " " " "
Foo:
BarFooFooColumnBarBarColumnpublic override void Down() { DropForeignKey("dbo.Bar", "BarColumn", "dbo.Foo"); Sql("TRUNCATE TABLE Foo"); AddForeignKey("dbo.Bar", "BarColumn", "dbo.Foo", "FooColumn", cascadeDelete: true); }
다음 스크립트는 모든 외부 키 제약 조건을 잘라내고 다시 만듭니다.
DECLARE @DROP_CONSTRAINT_SCRIPT VARCHAR(MAX);
DECLARE @CREATE_CONSTRAINT_SCRIPT VARCHAR(MAX);
SET @DROP_CONSTRAINT_SCRIPT='';
SET @CREATE_CONSTRAINT_SCRIPT='';
SELECT @DROP_CONSTRAINT_SCRIPT=@DROP_CONSTRAINT_SCRIPT + 'ALTER TABLE '+FK_Table+'
DROP CONSTRAINT '+FK_Name+';
',
@CREATE_CONSTRAINT_SCRIPT = @CREATE_CONSTRAINT_SCRIPT +
'ALTER TABLE '+FK_Table+'
ADD CONSTRAINT '+FK_Name+'
FOREIGN KEY ('+FK_Column+') REFERENCES '+PK_Table+'('+PK_Column+');
'
FROM (
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_SCHEMA FK_Schema
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_SCHEMA PK_Schema
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
, RC.MATCH_OPTION MatchOption
, RC.UPDATE_RULE UpdateRule
, RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KP.TABLE_NAME='TABLE_NAME_TRUNCATE'
) TBL
-- DROP CONSTRAINTS
EXEC( @DROP_CONSTRAINT_SCRIPT);
-------
-- TRUNCATE TABLE SCRIPT NEEDS TO BE PUT BELOW
-------
TRUNCATE TABLE TABLE_NAME_TRUNCATE
-- RECREATE CONSTRAINTS
EXEC( @CREATE_CONSTRAINT_SCRIPT);
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;
참조 - 외부 키 제약 테이블 잘라내기
MYSQL에서 일하는 중
번 시도해 보세요.DELETE FROM <your table >;.
서버에 제한의 이름과 테이블이 표시됩니다.또, 그 테이블을 삭제하면, 필요한 것을 삭제할 수 있습니다.
하위 테이블의 제약 조건을 먼저 비활성화하면 하위 테이블에 대한 외부 키 제약 조건이 있는 상위 테이블의 TRUNCATE 테이블을 사용할 수 있습니다.예.
하위 테이블의 외부 키 제약 조건 child_par_ref, PARENT_TAB 참조LE
ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref;
TRUNCATE TABLE CHILD_TABLE;
TRUNCATE TABLE PARENT_TABLE;
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref;
가장 쉬운 방법:
1 - phpmyadmin으로 입력합니다.
2 - 왼쪽 열의 테이블 이름을 클릭합니다.
3 - 조작(상단 메뉴)을 클릭합니다.
4 - [테이블 비우기(TRUNCATE)]을 클릭합니다.
5 - "외부 키 검사 활성화" 상자 사용 안 함
6 - 완료!
이미지 튜토리얼 링크
튜토리얼:http://www.imageno.com/wz6gv1wuqajrpic.html
(죄송합니다.이미지를 업로드 할 수 있는 충분한 평판이 없습니다.P )
언급URL : https://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint
반응형
'programing' 카테고리의 다른 글
| 열 변경, 기본 제약 조건 추가 (0) | 2023.04.07 |
|---|---|
| LIKE 필터에 밑줄 문자를 사용하면 모든 결과가 나타나는 이유는 무엇입니까? (0) | 2023.04.07 |
| 외부 키 제약이 SQL 서버에 있는 경우에만 어떻게 삭제합니까? (0) | 2023.04.07 |
| SQL Server 프로시저/트리거 내에서 텍스트를 찾는 방법 (0) | 2023.04.07 |
| 데이터베이스에서 상속을 효과적으로 모델링하려면 어떻게 해야 합니까? (0) | 2023.04.07 |