OraFAQ Forum: Client Tools » Disable and Enable Constraints

Home » Other » Client Tools » Disable and Enable Constraints ( Windows server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Disable and Enable Constraints [message #493265]

Mon, 07 February 2011 03:25

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

1.Can anyone please tell me how to disable and enable all constraints pertaining to one table at once.
I want to disable them at once and also enable them at once.
I make use of TOAD.

Regards,
Sankalp

Report message to a moderator

Re: Disable and Enable Constraints [message #493268 is a reply to message #493265]

Mon, 07 February 2011 03:28

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Schema Browser, Constraints tab. <Control>-click (i.e. select) all constraints you want to disable. Click the "Disable the selected constraints" gray bulb icon.

Report message to a moderator

Re: Disable and Enable Constraints [message #493270 is a reply to message #493265]

Mon, 07 February 2011 03:29

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> alter table emp disable constraint EMP_EMP_FK disable constraint EMP_DEPT_FK;

Table altered.

SQL> alter table emp enable constraint EMP_EMP_FK enable constraint EMP_DEPT_FK;

Table altered.

Regards
Michel

Regards
Michel

Report message to a moderator

Re: Disable and Enable Constraints [message #493271 is a reply to message #493268]

Mon, 07 February 2011 03:32

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

ok. is there a way to select only the constraints referenced by other tables.
For eg. DCODE table is referenced in 1000 other tables. How can i get a list if all those tables which has referenced one particular table(DCODE)

Report message to a moderator

Re: Disable and Enable Constraints [message #493274 is a reply to message #493271]

Mon, 07 February 2011 03:38

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

@Michel Cadot: IS " EMP_EMP_FK and EMP_DEPT_FK" constraints w.r.t EMP table?
If so i have 100's of constraints attached to my table. Am i supposed to type all of them?

I am a beginner. So if my questions sound very silly pls excuse me.

Report message to a moderator

Re: Disable and Enable Constraints [message #493275 is a reply to message #493274]

Mon, 07 February 2011 03:39

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Yes
2/ Yes or use TOAD or use SQL to generate the statement (this will learn you to query the dictionary (user_constraints view) and is a nice SQL exercise)

Regards
Michel

[Updated on: Mon, 07 February 2011 03:40]

Report message to a moderator

Re: Disable and Enable Constraints [message #493276 is a reply to message #493275]

Mon, 07 February 2011 03:41

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

@Michel Cadot: Thanks
Is there a way to select only the constraints referenced by other tables.
For eg. DCODE table is referenced in 1000 other tables. How can i get a list if all those tables which has referenced one particular table(DCODE)

Report message to a moderator

Re: Disable and Enable Constraints [message #493282 is a reply to message #493276]

Mon, 07 February 2011 04:03

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Link user_constraints to itself. Query the unique constraints on the table. Then link to the constraints that reference those constraints.
Look at the constraint_type and r_constraint_name columns

Report message to a moderator

Re: Disable and Enable Constraints [message #493287 is a reply to message #493282]

Mon, 07 February 2011 04:12

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

ok. u mean

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ABCD'

get R_CONSTRAINT_NAME from here.

select * FROM USER_CONSTRAINTS where constraint_name='R_CONSTRAINT_NAME'

This will return the table_name that references table 'ABCD' ??

[Updated on: Mon, 07 February 2011 04:14]

Report message to a moderator

Re: Disable and Enable Constraints [message #493289 is a reply to message #493287]

Mon, 07 February 2011 04:16

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Not quite. You get the constraint_name from the first query. You link that to r_constraint_name. It can be done as a single query.

R_constraint_name is the name of the constraint the fk references.

Report message to a moderator

Re: Disable and Enable Constraints [message #493291 is a reply to message #493289]

Mon, 07 February 2011 04:19

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

select * FROM USER_CONSTRAINTS where constraint_name=(SELECT R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ABCD')

Report message to a moderator

Re: Disable and Enable Constraints [message #493293 is a reply to message #493291]

Mon, 07 February 2011 04:27

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

I told you to swap constraint_name and r_constraint_name around.

Report message to a moderator

Re: Disable and Enable Constraints [message #493295 is a reply to message #493293]

Mon, 07 February 2011 04:43

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

ok. got it. Thanks a lot Smile

Report message to a moderator

Re: Disable and Enable Constraints [message #493298 is a reply to message #493295]

Mon, 07 February 2011 04:50

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

You'll want to change the = to an IN as well.

Report message to a moderator

Re: Disable and Enable Constraints [message #493316 is a reply to message #493298]

Mon, 07 February 2011 05:16

sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore

Member

Of course. Mods Kindly close this thread.
Thank u.

Report message to a moderator

Previous Topic:	Set up the attributes of SQL PLUS permanently.
Next Topic:	SQPLUS - How to to see specs/body of package procedures ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 17 08:41:49 CDT 2026