How Do I Ignore Errors When Deleting Records

问题:

I'm in the process of testing a large number of schema changes to upgrade our db to run with the latest version of a packaged product we have.

At this point I'm not interested in the data contained in the db, only the schema (i.e. the tables, views, constraints, keys, stored procedures, etc.).

My testing entails running scripts, resolving errors, an re-running the scripts. If I want to re-run the scripts I need to first restore the db to get it back to a known state. Restoring the db is very time consuming as it has lots of data. I would like to "slim down" the db and remove as much data as possible. That way it will be quicker to restore the db and re-run my scripts

When I attempt to delete records from many of the tables ("delete from table-name") I run into constraint errors and the command stops.

Is there a way to allow the command to continue and, in effect, delete all the records in the table where there aren't constraint issues? In other words I'd like the command to ignore errors and continue to delete all the records it can.

Any Suggestions would be greatly appreciated.

回答1:

Byron above makes a good point; you can disable FOREIGN KEY or CHECK constraints with the ALTER TABLE command:

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

Then do your work, and when finished,

ALTER TABLE TableName CHECK CONSTRAINT ALL

to re-enable constraints. Be careful, though: if you leave your data in an inconsistent state that violates constraints once you re-enable them, future updates can fail due to constraint errors.

Sources:
http://weblogs.sqlteam.com/joew/archive/2008/10/01/60719.aspx

http://technet.microsoft.com/en-us/library/ms190273.aspx