Re: how to delete constraints?

Otis Gospodnetic schrieb in Nachricht <86qkba$1mb$1_at_nnrp1.deja.com>...
>How does one go about deleting/droping constraints?
>
>delete from user_constrains where owner = 'MYUSER'
>
>Like that maybe? (I don't want to just do it and mess it up :))

Otis, please noooooo !

you shouldn't fiddle around in the data dictionary like this ;-)) - don't substitute DDL with DML - you might wrap up everything ... you can query all these funny tables - of course, but changes should be made via the DDL-commands:

alter table xyz drop constraint constraint_name;

thats it - that shows the importance of giving names to your constraints at table creation time, if you don't name them, the system does it for you (SYS_Cnnnnnn ...) and you got to find the name in USER_CONSTRAINTS first ...

if you want to drop all your constraints you might as well spool the output of a SELECT like:

select 'alter table ' || table_name || ' drop constraint ' || constraint_name || ';'
from user_constraints
where owner = user;

that gives you a script file (you should remove the header and trailer lines if you didn't turn all that off before spooling) which contains a drop statement for every constraint defined on your login_users tables -

execute it (@filename) -

there they go -

similarily, by restricting the select above with a table_name in a where clause, you can create a script to drop all constraints on a given table ...

hth Received on Fri Jan 28 2000 - 04:22:08 CST