Recently you need to delete content in some tables when you do fake information. but:
After setting the foreign key, you want to delete the data in the table that cannot be deleted. When you need to delete the foreign key, rebuild, or find the foreign key, use the Alter Table Name Nocheck foreign key name to temporarily shield the outer key, then delete. Simply write a stored procedure, set the switch of the foreign key. Exec fk_switch 'table name, 0 shield foreign key exec FK_Switch' table name, 1 Restart foreign key
/ * Usage: exec fk_switch 'tableName', 0delete tableName where fieldName = 'abc' - truncate table tableNameexec fk_switch 'tableName', 1 * / Create proc fk_switch @tableName varchar (20), @ status bitAsdeclare @fk varchar (50) , @ fktable varchar (20) declare @s varchar (1000) declare cur cursor for select b.name as fkname, c.name as fktablename from sysforeignkeys a join sysobjects b on a.constid = b.id join sysobjects c on a. fkeyid = c.id join sysobjects d on a.rkeyid = d.id where d.name = @tableNameopen curfetch next from cur into @ fk, @ fktablewhile @@ fetch_status = 0begin if @status = 0 begin set @s = 'alter table ' @ fktable ' nocheck constraint ' @fk print @s end else begin set @s =' alter table ' @ fktable ' check constraint ' @fk print @s end exec (@s) fetch next from cur into @ fk, @ fktablendclose curdeallocate Cur
Go
- The following is the test: CREATE TABLE A (ID INT Primary Key) GOCREATE TABLE B (ID INT, ConsTRAINT FK_B_A Foreign Key (id) References A (ID)) GOCREATE TABLE C (ID INT, Constraint FK_C_A Foreign Key (ID) A (ID)) GoInsert a Values (1) Insert B Values (1) Insert C Values (1)
--1: delete a / ***** Server: Message 547, Level 16, State 1, Row 1Delete Statement Conflicted with Column Reference Constraint 'fk_b_a'. The Conflict Occurred In Database 'Pubs', Table 'B', Column 'id'.The Statement Has Been Terminated. ******* / - 2: Begin Traexec Fk_Switch' A ', 0Delete a EXEC FK_SWITCH' A ', 1 ROLLBACK / * ALTER TABLE B NOCHECK Constraint Fk_b_aalter Table C Nocheck ConsTRAINT FK_C_A
(The number of rows affects is 1 line)
ALTER TABLE B CHECK ConsTRAINT FK_B_AALTER TABLE C Check ConsTRAINT FK_C_A * /
--3: Clear test table Drop Table A, B, CGO
