Home » SQL & PL/SQL » Client Tools » Disable and Enable Constraints ( Windows server 2003)
Disable and Enable Constraints [message #493265] Mon, 07 February 2011 03:25 Go to next message
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
Re: Disable and Enable Constraints [message #493268 is a reply to message #493265] Mon, 07 February 2011 03:28 Go to previous messageGo to next message
Littlefoot
Messages: 21331
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.
Re: Disable and Enable Constraints [message #493270 is a reply to message #493265] Mon, 07 February 2011 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Disable and Enable Constraints [message #493271 is a reply to message #493268] Mon, 07 February 2011 03:32 Go to previous messageGo to next message
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)
Re: Disable and Enable Constraints [message #493274 is a reply to message #493271] Mon, 07 February 2011 03:38 Go to previous messageGo to next message
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.
Re: Disable and Enable Constraints [message #493275 is a reply to message #493274] Mon, 07 February 2011 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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)
Re: Disable and Enable Constraints [message #493282 is a reply to message #493276] Mon, 07 February 2011 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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
Re: Disable and Enable Constraints [message #493287 is a reply to message #493282] Mon, 07 February 2011 04:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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.
Re: Disable and Enable Constraints [message #493291 is a reply to message #493289] Mon, 07 February 2011 04:19 Go to previous messageGo to next message
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')
Re: Disable and Enable Constraints [message #493293 is a reply to message #493291] Mon, 07 February 2011 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
I told you to swap constraint_name and r_constraint_name around.
Re: Disable and Enable Constraints [message #493295 is a reply to message #493293] Mon, 07 February 2011 04:43 Go to previous messageGo to next message
sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore
Member
ok. got it. Thanks a lot Smile
Re: Disable and Enable Constraints [message #493298 is a reply to message #493295] Mon, 07 February 2011 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll want to change the = to an IN as well.
Re: Disable and Enable Constraints [message #493316 is a reply to message #493298] Mon, 07 February 2011 05:16 Go to previous message
sankalputtara
Messages: 53
Registered: January 2011
Location: Bangalore
Member
Of course. Mods Kindly close this thread.
Thank u.
Previous Topic: Set up the attributes of SQL PLUS permanently.
Next Topic: SQPLUS - How to to see specs/body of package procedures ?
Goto Forum:
  


Current Time: Fri Jan 19 16:36:41 CST 2018

Total time taken to generate the page: 0.05354 seconds