Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harshal_in
Aged Yak Warrior
633 Posts |
 Posted - 2002-11-09 : 07:46:51
|
| hello,I have a master table which has many tables related to it with foriegn key and primary key constraints . now is I want to delete/truncate all the records from this master table ,but won't allow me to do so.It says 'can not truncate the master table because it is being referenced by a foriegn key constraint.Is there any way to override the foreign key constraints while deleting or inserting a record in a table?thankyou!harshal. |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-11-09 : 08:30:18
|
| You cannot use TRUNCATE TABLE command on a table referenced by FOREIGN KEY constraints. You need to drop the constraints first and then truncate.However, you can temporarily disable a FOREIGN KEY constraint (Using: ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName), and run a DELETE command on the primary key table.By doing this you are putting your referential integrity at risk. Best thing to do would be, to delete the rows from foreign key tables first, and then delete the rows from primary key tables.--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
burbakei
Yak Posting Veteran
80 Posts |
Posted - 2002-11-13 : 21:27:16
|
| you can use the system table sysforeignkeys to generate a script and then run that script.SELECT 'ALTER TABLE ' + OBJECT_NAME(fkeyid) + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(constid)FROM sysforeignkeysWHERE OBJECT_NAME(fkeyid) = 'MasterTable'GROUP BY fkeyid, constidand then by changing NOCHECK to CHECK you enable your constraints again. if you want to execute a script to disable constraintsyou can use the above SELECT with a cursor or you use a temporary table. |
|
|
|
|
|
|
constraint overriding