Negalima apdailinti stalo, nes UŽSIENIO RINKINIO apribojimas reiškia jį?

Naudodamas MSSQL2005, galiu trinti lentelę su užsienio raktų suvaržymu, jei pirmiausia sutrumpinu vaiko stalą (lentelę su pagrindiniu FK santykio raktu)?

Aš žinau, kad taip pat galiu

  • Naudokite DELETE be vietos, kur yra, tada RESEED identifikatorius (arba)
  • Ištrinkite FK, supjaustykite stalą ir vėl sukurkite FK.

Maniau, kad tol, kol aš sutrumpinsiu vaiko stalą prieš patroną, būčiau gerai, nevykdydamas nė vienos iš pirmiau minėtų parinkčių, bet gaunu šią klaidą:

Negalima sutrumpinti lentelės „TableName“, nes UŽSIENIO REIKŠMĖS apribojimas reiškia jį.

376
31 окт. ctrlShiftBryan nustatė spalio 31 d 2008-10-31 18:06 '08 6:06 val. 2008-10-31 18:06
@ 26 atsakymai

Tai tiesa; Negalite trinti lentelės, kurioje yra FK riba.

Paprastai šis procesas yra:

  • Pašalinti apribojimus
  • Trim lentelė
  • Atkurti apribojimus.

(Žinoma, visi sandoryje.)

Žinoma, tai taikoma tik tada, kai vaikas jau yra sutrumpintas. Priešingu atveju, aš seku kitą kelią, visiškai priklausomai nuo to, kaip mano duomenys atrodo. (Jei norite įvesti per daug kintamųjų).

Nustatytas originalus plakatas KODĖL tai yra; daugiau informacijos žr.

319
31 окт. John Rudy atsakymas spalio 31 d 2008-10-31 18:08 '08, 18:08, 2008-10-31 18:08
 DELETE FROM TABLENAME DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME',RESEED, 0) 
border=0

Atkreipkite dėmesį, kad tai tikriausiai ne jums reikia, jei turite milijonų + įrašų, nes tai labai lėta.

293
02 авг. atsakymas pateikiamas s15199d 02 rug . 2012-08-02 23:01 '12 11:01 val. 2012-08-02 23:01

Kadangi TRUNCATE TABLE yra DDL komanda , ji negali patikrinti, ar lentelėje esantys įrašai yra susiję su įrašais, esančiais vaikų lentelėje.

Štai kodėl DELETE veikia, tačiau TRUNCATE TABLE : nes duomenų bazė gali užtikrinti, kad joks kitas įrašas nesusijęs su juo.

165
31 окт. atsakymas pateikiamas ctrlShiftBryan 31 okt. 2008-10-31 18:36 '08, 18:36, 2008-10-31 18:36

Be 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|1]) 

Kaip saugoma procedūra

https://github.com/reduardo7/TableTruncate

Atkreipkite dėmesį, kad tai tikriausiai ne jums reikia, jei turite milijonų + įrašų, nes tai labai lėta.

79
11 июля '14 в 23:41 2014-07-11 23:41 atsakymą pateikė Eduardo Cuomo liepos 14 d. 14 d. 23:41 2014-07-11 23:41

Pirmiau pateiktas „@denver_citizen“ sprendimas man neveikė, bet man patiko jo dvasia, todėl pakeitiau keletą dalykų:

  • atliko saugomą procedūrą
  • pakeitė užsienio raktų pildymo ir atkūrimo būdą.
  • šaltinio scenarijus sutrumpina visas atskaitos lenteles, tai gali sukelti užsienio raktų pažeidimo klaidą, jei atskaitos lentelėje yra kitų nuorodų į užsienio raktus. Šis scenarijus sutrumpina tik lentelę, nurodytą kaip parametras. Vartotojas gali išsaugoti šią saugomą procedūrą kelis kartus visose lentelėse teisinga tvarka.

Visuomenės labui čia yra atnaujintas scenarijus:

 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...  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 
53
06 нояб. Peter Szanto atsakymas lapkričio 06 2012-11-06 13:37 '12 13:37 2012-11-06 13:37

ištrindami visas šios lentelės eilutes, naudokite šią komandą naudodami ištrynimo pareiškimą

 delete from tablename DBCC CHECKIDENT ('tablename', RESEED, 0) 

EDIT: pataisyta SQL Server sintaksė

18
10 авг. atsakymas duotas abdelwahed 10 rug . 2013-08-10 23:15 '13, 23:15, 2013-08-10 23:15

Čia yra scenarijus, kurį parašiau norint automatizuoti procesą. Tikiuosi, kad tai padės.

 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' 
11
14 июня '10 в 20:28 2010-06-14 20:28 atsakymas duotas denver_citizen birželio 14 d., 10 val

Jūs galite sekti šį žingsnį. reseeding table galite ištrinti duomenis lentelėje.

 delete from table_name dbcc checkident('table_name',reseed,0) 

jei įvyksta tam tikra klaida, reikia iš naujo įkelti pagrindinę lentelę.

11
09 дек. Rajneesh Kumar atsakymas 09 d. 2013-12-09 08:25 '13, 08:25 am 2013-12-09 08:25

Rastas kitur internete.

 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' 
9
03 марта '11 в 8:46 2011-03-03 08:46 atsakymą pateikė Freddie Bell kovo 03 d. 11 val. 08:46 2011-03-03 08:46

Na, nes neradau labai paprasto sprendimo pavyzdžių, kuriuos naudoju:

  • Išjungti užsienio raktą;
  • Apipjaustymo lentelė
  • Atkurti užsienio raktą

Čia jis yra:

1) Raskite svetimojo rakto pavadinimą, kuris sukėlė gedimą (pvz., FK_PROBLEM_REASON, su ID laukeliu iš lentelės TABLE_OWNING_CONSTRAINT ) 2) Pašalinkite šį raktą iš lentelės:

 ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON 

3) lentelė, reikalaujanti sutrumpinimo

 TRUNCATE TABLE TABLE_TO_TRUNCATE 

4) Dar kartą pridėkite raktą į šią pirmąją lentelę:

 ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID) 

Kas tai yra.

9
29 янв. Lauro Wolff Valente Sobrinho atsakė į 29 m 2014-01-29 19:37 '14, 19:37, 2014-01-29 19:37

Jei teisingai suprantu, ką norite padaryti, sukurkite švarią duomenų bazės aplinką su integracijos testais.

Mano požiūris čia yra pašalinti visą schemą ir ją atkurti vėliau.

Priežastys:

  • Tikriausiai jau turite scenarijų „sukurti schemą“. Pakartotinis naudojimas bandymams atskirti yra paprastas.
  • Schemos sukūrimas yra gana greitas.
  • Naudodami šį metodą, gana paprasta nustatyti scenarijų, kad kiekvienas prietaisas sukurtų naują grandinę (su laikinu pavadinimu), o tada galite paleisti bandomuosius instrumentus lygiagrečiai, kad lėtiausia jūsų testo rinkinio dalis būtų kur kas greičiau.
6
06 мая '10 в 9:33 2010-05-06 09:33 atsakė Ken Egozi gegužės 06 d. 10 val. 9:33 2010-05-06 09:33

Lentelę negalite supjaustyti, nebent atsisakote apribojimų. Išjungimas taip pat neveikia. jums reikia viską iš naujo nustatyti. Aš sukūriau scenarijų, kuris pašalina visas įtampas ir po to atkuria.

Būtinai suvyniokite jį į sandorį;)

 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 
5
18 июля '13 в 21:58 2013-07-18 21:58 atsakymą pateikė renanleandrof liepos 18 d. , 13:21, 2013-07-18 21:58
 SET FOREIGN_KEY_CHECKS = 0; truncate table "yourTableName"; SET FOREIGN_KEY_CHECKS = 1; 
4
09 авг. atsakymą pateikė Victor Jimenez 09 rug. 2017-08-09 20:36 '17, 8:36 pm 2017-08-09 20:36

Truncate man neveikė, pašalinti + reseed yra geriausias būdas. Jei kai kurie iš jūsų turi daugybę lentelių, kad galėtumėte ištrinti ištrynimą, gali kilti problemų dėl kai kurių lentelių, kuriose nėra identifikatoriaus stulpelio, o toliau nurodytas kodas patikrina, ar prieš bandant iš naujo paleisti identifikatorių stulpelį

  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 
3
24 марта '16 в 22:20 2016-03-24 22:20 atsakymas duodamas „ Ji_in_coding“ kovo 24 d. 16, 22:20 2016-03-24 22:20

Rašau šiuos metodus ir bandau juos parametruoti, kad galėtumėte juos paleisti Query document arba Lengva daryti su juo SP .

A) Ištrinti

Jei jūsų lentelėje nėra milijonų įrašų , jis veikia gerai ir neturi jokių Alter komandų :

 --------------------------------------------------------------- ------------------- 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) 
  • Pirmiau pateiktame atsakyme aptariamos problemos sprendimo būdas pagrįstas @ s15199d atsakymu .

B) Triktis

Jei skaičiuoklėje yra milijonų įrašų arba turite problemų su kodų „ Alter“ komanda , naudokite šią komandą:

 -- 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 
  • Pirmiau pateiktame atsakyme šis problemos sprendimo būdas pagrįstas @LauroWolffValenteSobrinho atsakymu .

  • Jei turite daugiau nei vieną CONSTRAINT, pirmiau pateikiamoje užklausoje turite pridėti savo kodus, kaip ir mane.

  • Taip pat galite pakeisti pirmiau nurodytą kodo bazę @SerjSagan atsakymą, kad išjungtumėte įgalinimo ribą

2
25 июля '17 в 4:47 2017-07-25 04:47 atsakymas duotas RAM liepos 25 d. 17, 04:47 2017-07-25 04:47

Tai mano sprendimas šiai problemai spręsti. Aš naudoju jį pakeisti kompiuterį, bet idėja yra tokia pati. Tikiuosi, kad tai bus naudinga

 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  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' 
2
03 окт. atsakymas pateikiamas Olegas 03 okt. 2012-10-03 17:29 '12, 17:29, 2012-10-03 17:29

Следующее работает для меня даже с ограничениями FK и объединяет следующие ответы только для удаления указанных таблиц :


 USE [YourDB]; DECLARE @TransactionName varchar(20) = 'stopdropandroll'; BEGIN TRAN @TransactionName; set xact_abort on;  -- ===== DO WORK // ===== -- dynamic sql placeholder DECLARE @SQL varchar(300); -- LOOP: /questions/21378/comma-separated-list-in-sql/158577#158577 -- 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): /questions/21380/using-a-variable-for-table-name-in-from-clause-in-sql-server-2008/158590#158590 SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- /questions/21344/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint/158383#158383 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);'; 
2
ответ дан drzaus 01 авг. '13 в 23:03 2013-08-01 23:03

Для 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 
2
ответ дан Serj Sagan 29 июля '13 в 7:55 2013-07-29 07:55