can some one help me on constraints

[venugopalb]

Hi ,

have a doubt in constraints ie referential, foreign key.


now i create a test table

CREATE TABLE TEST (COL1 VARCHAR2(10) PRIMARY KEY);

CREATE TABLE TEST1 (COL1 VARCHAR2(10) PRIMARY KEY,
COL2 VARCHAR2(10) );


CREATE TABLE TEST2(COL_1 REFERENCES TEST1(COL1),
COL_2 VARCHAR2(10));

WHICH SYSTEM TABLE WILL YOU QUEERY TO
FIND OUT WHICH TABLE IS COL_1 OF TEST2 WILL REFERENCE TO .

THE TABLE USER_CONS_COLUMNS AND USER_CONSTRAINTS GIVES THE CONSTRAINT NAME AND CONSTRAINT TYPE BUT WILL NOT GIVE WHICH TABLE IS
THIS COLUMN COL_1 REFFERING TO

CAN SOME ONE HELP ME OUT

[ales]

Hi,
it's the USER_CONSTRAINTS.R_CONSTRAINT_NAME column.
Foreign keys reference primary keys, not columns.
Ales

[Shyamis]

Hi,

try out

select table_name from user_constraints where constraint_name in (select r_constraint_name from user_constraints)
/

This should give you a list of table names your referential constraint is looking at! The logic behind this is the fact that the foreign key looks at the primary key of another table so there should be a valid record of that in the same user_constraints table!

Thanks,
have a nice day,
-Shyami