Home » SQL & PL/SQL » SQL & PL/SQL » Disable Constraints from Parent and Child tables
Disable Constraints from Parent and Child tables [message #281904] Mon, 19 November 2007 22:41 Go to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Hi All,

I need help to disable all constraints from parent table and only reference keys from child table.
I wrote below code disabling fine but when i try enable with similar code using option status='DISABLED' not enabling all constraints.
My Code :

CREATE OR REPLACE PROCEDURE JC_DISABLE(TNAME IN user_constraints.TABLE_NAME%TYPE)
IS
BEGIN

for j IN (select fk.table_name , fk.constraint_name from user_constraints fk, user_constraints pk
where fk.CONSTRAINT_TYPE = 'R'
and fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and
pk.TABLE_NAME=TNAME and fk.status='ENABLED')
loop
EXECUTE IMMEDIATE 'alter table ' ||j.table_name|| ' disable constraint ' ||j.constraint_name;
end loop j;

for i IN (select table_name, constraint_name from user_constraints
where TABLE_NAME=TNAME
and status = 'ENABLED')
loop
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
end JC_DISABLE;

[Updated on: Tue, 20 November 2007 06:19]

Report message to a moderator

Re: Disable Constraints from Parent and Child tables [message #281907 is a reply to message #281904] Mon, 19 November 2007 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but not working properly
My car is not working properly.
Look at a picture of my car & tell me how to fix it, please.
Re: Disable Constraints from Parent and Child tables [message #281908 is a reply to message #281904] Mon, 19 November 2007 23:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Your code looks fine.. What error you got ?

Thumbs Up
Rajuvan.
Re: Disable Constraints from Parent and Child tables [message #281918 is a reply to message #281904] Tue, 20 November 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Avoid red font.

Regards
Michel
Re: Disable Constraints from Parent and Child tables [message #281988 is a reply to message #281907] Tue, 20 November 2007 05:16 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
anacedent wrote on Tue, 20 November 2007 05:02

>but not working properly
My car is not working properly.
Look at a picture of my car & tell me how to fix it, please.

Well, looking at the picture it would appear that you car is in fact a several cardboard boxes covered in tin foil. Therein lies the problem Smile
Re: Disable Constraints from Parent and Child tables [message #282001 is a reply to message #281904] Tue, 20 November 2007 06:00 Go to previous messageGo to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
I am a new user,if i did not follow rules i am very sorry.
My code is not hitting any error but while enabling all constraints are not enabling.Same code i used for enabling with STATUS=DISABLED option.
Please encourage and help new users.How can car example help a new user.
Re: Disable Constraints from Parent and Child tables [message #282007 is a reply to message #282001] Tue, 20 November 2007 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you mean that some of the constraints are not enabling, or all of them are not enabling?

It's not something as simple as forgetting to change the order of the loops so that you enable Primary Keys before Foreign Keys is it?

And you remembered to change the Execute Immediates to use 'ENABLE' rather than 'DISABLE', didn't you.

It's hard to tell what the problem is when you don't post the actual code that is causing the problem.

[Updated on: Tue, 20 November 2007 06:26]

Report message to a moderator

Re: Disable Constraints from Parent and Child tables [message #282015 is a reply to message #282001] Tue, 20 November 2007 07:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Instead of using execute immediate, use a dbms_output.put_line in the dynamically built strings and show us what the output is (note, PLEASE copy and paste this result, do not type the result into a reply
Re: Disable Constraints from Parent and Child tables [message #282114 is a reply to message #281904] Tue, 20 November 2007 22:58 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

is this what happened to you ?
Then Modify the script as suggested .

SQL> CREATE OR REPLACE PROCEDURE JC_DISABLE(TNAME IN user_constraints.TABLE_NAME
%TYPE)
  2  IS
  3  BEGIN
  4
  5  for j IN (select fk.table_name , fk.constraint_name
  6               from user_constraints fk, user_constraints pk
  7               where fk.CONSTRAINT_TYPE = 'R'
  8               and fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  9               and pk.TABLE_NAME=TNAME and fk.status='ENABLED')
 10  loop
 11  DBMS_OUTPUT.PUT_LINE( 'alter table ' ||j.table_name|| ' disable constraint
' ||j.constraint_name );
 12  EXECUTE IMMEDIATE 'alter table ' ||j.table_name|| ' DISABLE constraint ' ||
j.constraint_name;
 13  end loop j;
 14
 15  for i IN (select table_name, constraint_name from user_constraints
 16            where TABLE_NAME=TNAME
 17            and status = 'ENABLED')
 18  loop
 19  DBMS_OUTPUT.PUT_LINE( 'alter table ' ||i.table_name|| ' disable constraint
' ||i.constraint_name);
 20  EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' DISABLE constraint ' ||
i.constraint_name;
 21  end loop i;
 22
 23  end JC_DISABLE;
 24  /

Procedure created.

SQL> DECLARE
  2    TNAME VARCHAR2(30);
  3
  4  BEGIN
  5    TNAME := 'CB_ACC_BALNC';
  6
  7    MTN4C_TRG.JC_DISABLE ( TNAME );
  8    COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select CONSTRAINT_TYPE, STATUS from user_constraints where TABLE_NAME ='CB_
ACC_BALNC';

C STATUS
- --------
C DISABLED
C DISABLED
C DISABLED
P DISABLED

SQL> CREATE OR REPLACE PROCEDURE JC_ENABLE(TNAME IN user_constraints.TABLE_NAME%
TYPE)
  2  IS
  3  BEGIN
  4
  5  for j IN (select fk.table_name , fk.constraint_name
  6               from user_constraints fk, user_constraints pk
  7               where fk.CONSTRAINT_TYPE = 'R'
  8               and fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  9               and pk.TABLE_NAME=TNAME and fk.status='DISABLED')
 10  loop
 11  EXECUTE IMMEDIATE 'alter table ' ||j.table_name|| ' ENABLE constraint ' ||j
.constraint_name;
 12  end loop j;
 13
 14  for i IN (select table_name, constraint_name from user_constraints
 15            where TABLE_NAME=TNAME
 16            and status = 'DISABLED')
 17  loop
 18  EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' ENABLE constraint ' ||i
.constraint_name;
 19  end loop i;
 20
 21  end JC_ENABLE;
 22  /

Procedure created.

SQL> DECLARE
  2    TNAME VARCHAR2(30);
  3
  4  BEGIN
  5    TNAME := 'CB_ACC_BALNC';
  6
  7    MTN4C_TRG.JC_ENABLE ( TNAME );
  8    COMMIT;
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
ORA-06512: at "MTN4C_TRG.JC_ENABLE", line 11
ORA-06512: at line 7


SQL> select CONSTRAINT_TYPE, STATUS from user_constraints where TABLE_NAME ='CB_
ACC_BALNC';

C STATUS
- --------
C DISABLED
C DISABLED
C DISABLED
P DISABLED

SQL> CREATE OR REPLACE PROCEDURE JC_ENABLE(TNAME IN user_constraints.TABLE_NAME%
TYPE)
  2  IS
  3  BEGIN
  4
  5  for i IN (select table_name, constraint_name
  6               from user_constraints
  7               where TABLE_NAME=TNAME
  8               and status = 'DISABLED')
  9  loop
 10  EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' ENABLE constraint ' ||i
.constraint_name;
 11  end loop i;
 12
 13  for j IN (select fk.table_name , fk.constraint_name
 14               from user_constraints fk, user_constraints pk
 15               where fk.CONSTRAINT_TYPE = 'R'
 16               and fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 17               and pk.TABLE_NAME=TNAME and fk.status='DISABLED')
 18  loop
 19  EXECUTE IMMEDIATE 'alter table ' ||j.table_name|| ' ENABLE constraint ' ||j
.constraint_name;
 20  end loop j;
 21
 22  end JC_ENABLE;
 23  /

Procedure created.

SQL> DECLARE
  2    TNAME VARCHAR2(30);
  3
  4  BEGIN
  5    TNAME := 'CB_ACC_BALNC';
  6
  7    MTN4C_TRG.JC_ENABLE ( TNAME );
  8    COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select CONSTRAINT_TYPE, STATUS from user_constraints where TABLE_NAME ='CB_
ACC_BALNC';

C STATUS
- --------
C ENABLED
C ENABLED
C ENABLED
P ENABLED

SQL>


Thumbs Up
Rajuvan
Previous Topic: Function calling
Next Topic: Need help on SQL statement ..
Goto Forum:
  


Current Time: Sat Dec 10 20:21:47 CST 2016

Total time taken to generate the page: 0.28074 seconds