Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL query for foreign keys?
Hi,
try this
SELECT 'select ' || cl.column_name || ' from ' || pk.table_name || ';' FROM user_cons_columns cl,
( SELECT DISTINCT dt.constraint_name, m.table_name FROM user_constraints m, -- table with PK user_constraints dt -- table with FK WHERE m.table_name LIKE UPPER( '%' ) AND m.constraint_name = dt.r_constraint_name ) pkWHERE cl.constraint_name = pk.constraint_name;
the result will be something like this
DYNAMIC_STATEMENT
Put in script and run it or give the result to a varchar and then execute a dynamic sql. That works only with primary key that have only one column and you need a description column for your list_box i think.
hth
Kay
Mail: kkanekowski_at_mindcommerce.de
"Stan Brown" <stanb_at_panix.com> schrieb im Newsbeitrag news:9jjuof$86f$1_at_panix3.panix.com...
> In <9jj4n4$gi9$1_at_fermi.tro.net> "Kay Kanekowski" <kkanekowski_at_mindcommerce.de> writes:
>
> >Hi ,
> >try this
> >SELECT *
> > FROM user_cons_columns
> > WHERE constraint_name IN
> > ( SELECT DISTINCT dt.constraint_name
> > FROM user_constraints m, -- table with PK
> > user_constraints dt -- table with FK
> > WHERE m.table_name = UPPER( '&1' )
> > AND m.constraint_name = dt.r_constraint_name );
>
>
> CREATE TABLE brkr (
> brkr VARCHAR(5) NOT NULL ,
> brkr_level NUMBER (2,0) NOT NULL ,
> descript VARCHAR(80) NOT NULL ,
> gen NUMBER(1,0) NOT NULL ,
> bought NUMBER(1,0) NOT NULL ,
> consume NUMBER(1,0) NOT NULL ,
> meter_id NUMBER (2,0),
> CONSTRAINT brkr_index
> PRIMARY KEY (brkr, brkr_level)
> USING INDEX
> TABLESPACE CONFIG_TS ,
> FOREIGN KEY (meter_id) REFERENCES meter,
> CHECK (gen IN(0,1)),
> CHECK (bought IN(0,1)),
> CHECK (consume IN(0,1))
> )
> TABLESPACE CONFIG_TS ;
>
>
> I need to able to retrieve the fact the column meter_id of this table
> (brkr) has the column meter_id in table meter.
>
> I am going to use this to structure a query to return all meter_id's in
> meter, and present this as a scrolling list on the input form for brkr.
>
> Am I misunderstanding?
>
Received on Tue Jul 24 2001 - 09:47:21 CDT
![]() |
![]() |