How can i get the definition of a foreign key from the ALL_CONSTRAINTS,
ALL_CONS_COLUMNS?.
The View ALL_CONS_COLUMNS Shows me only the columns of the foreign key
table and not the
references column. Who can help?
/
DROP TABLE KOPF;
DROP TABLE ZEILE;
/
CREATE TABLE KOPF(
K_NR NUMBER( 10, 0 ),
K_KUNDE NUMBER( 10, 0 ),
CONSTRAINT KOPF_PK PRIMARY KEY ( K_NR ) )
/
CREATE TABLE ZEILE(
Z_NR NUMBER( 10, 0 ),
Z_POS NUMBER( 10, 0 ),
Z_ARTIKEL NUMBER( 10, 0 ),
CONSTRAINT ZEILE_PK PRIMARY KEY( Z_NR, Z_POS ),
CONSTRAINT ZEILE_FK FOREIGN KEY( Z_NR ) REFERENCES KOPF( K_NR ) )
/
SELECT * FROM ALL_CONSTRAINTS
WHERE OWNER = 'BOKU' AND TABLE_NAME IN( 'KOPF', 'ZEILE' )
/
RESULT:
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER
R_CONSTRAINT_NAME
KOPF_PK P KOPF
ZEILE_FK R ZEILE BOKU KOPF_PK NO ACTION
ZEILE_PK P ZEILE
END RESULT:
/
SELECT * FROM ALL_CONS_COLUMNS
WHERE OWNER = 'BOKU' AND TABLE_NAME IN( 'KOPF', 'ZEILE' )
/
RESULT:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
KOPF_PK KOPF K_NR 1
ZEILE_FK ZEILE Z_NR 1
ZEILE_PK ZEILE Z_NR 1
ZEILE_PK ZEILE Z_POS 2
END RESULT:
Received on Fri Jul 11 1997 - 00:00:00 CDT
