Re: SQL query for foreign keys?

From: Kay Kanekowski <kkanekowski_at_mindcommerce.de>
Date: Tue, 24 Jul 2001 16:47:21 +0200
Message-ID: <9jk20e$n4u$1@fermi.tro.net>

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 ) pk

WHERE cl.constraint_name = pk.constraint_name;

the result will be something like this

DYNAMIC_STATEMENT

---

select METER_ID from METER;

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