Re: Primary Key definition from data dictionary

Foreign keys are built on constraints. the column R_CONSTRAINT_NAME can be used to join back to the all_cons_columns to give you the columns that the foreign key is built on.

ACE <mario.simandl_at_ace.co.at> wrote in article <01bc8dd4$2ab947a0$01608ba4_at_acesrvwi>...
> 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