File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Drop All Constraints? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Drop All Constraints?" Watch "Drop All Constraints?" New topic

Drop All Constraints?

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Is there a good way to drop all constraints on a table (in SQL Server)? I'd love to be able to do something like this:


SCJP Tipline, etc.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Its not quite that easy. In 2000 you could select everything from systobject where the parent_obj = your table and the xtype is a constraint, then use a cursor to go through these constructing and executing the "alter table [your table] drop constraint [current_constraint]" commands.

Have a look at the docs for sysobjects. That should get you going.

If you want to do this in a 2005 style, there are now four constraint views (sys.check_constraints, sys.default_constraints, sys.foreign_keys and sys.key_constraints) that will give you all the various constrains for your table.

Have a read of the docs for these tables. Its also a good think to know about the object_name and object_id functions. These will transform from an id into a name and back again. So typically you might use them to do this:

or something or other.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
krishna chaitanya vemprala

Joined: Feb 11, 2008
Posts: 1

By running the above query, it generates you a list of all tables and its respective constraints, the present query generates the alter table statements for removing primary and foreign keys only, you can add ('PK','F','U') to the list if you have unique keys also. copy the result list and execute that and the constraints will be disabled.

I hope the problem will be solved by this

Thank you.

Vemprala Chaitanya.
I agree. Here's the link:
subject: Drop All Constraints?
jQuery in Action, 3rd edition