viewing constraint on tables cascade [message #75251] |
Tue, 20 August 2002 23:32 |
SHARMA POLPE
Messages: 5 Registered: July 2002
|
Junior Member |
|
|
i am unable to disable constraints for insertion of
data exm. A,B,C are tables which are having some Primary,Foreign,Ref Keys is there any method to know the references at one stroke or in any manner,
please help me
(i would like to know master child table references for the above three exmp. tables of A,B,C,)
bye
psr
|
|
|
Re: viewing constraint on tables cascade [message #75255 is a reply to message #75251] |
Mon, 26 August 2002 21:26 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
CREATE TABLE x (
x1 NUMBER)
Table created
alter table x add constraint x_pk primary key (x1)
Table altered
CREATE TABLE y (
y1 NUMBER primary key,
x1 NUMBER )
Table created
CREATE TABLE z (
z1 NUMBER primary key,
y1 NUMBER )
Table created
alter table y add constraint y_fk foreign key (x1) references x
Table altered
alter table z add constraint z_fk foreign key (y1) references y
Table altered
alter table x disable constraint x_pk cascade
Table altered
select table_name, constraint_name, constraint_type, status
from user_constraints
where table_name in ('X', 'Y')
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ --------------- --------
X X_PK P DISABLED
Y SYS_C0018108 P ENABLED
Y Y_FK R DISABLED
3 rows selected
Look at CONSTRAINT_NAME, R_CONSTRAINT_NAME in user_constraints. Look for your primary key ('P') on your parent table in the R_CONSTRAINT_NAME column. That'll list the FK's. You should be able to query USER_CONSTRAINTS using "connect by" but watch out for loops if you are trying to navigate the "tree"
|
|
|