A copy of this was sent to aacesta2_at_my-dejanews.com
(if that email address didn't require changing)
On Mon, 14 Dec 1998 10:49:15 GMT, you wrote:
>Hello,
>
>I couldn't reconstruct a foreign key information through the data
>dictionary. I can reconstruct a primary key information, but not foreign key.
>
>I want to be able to do a select in the data dictionary that returns
>information equivalente to the foreign key creation sql:
>
>ALTER TABLE SN_REL_CONTRATO_PRODUTO ADD (
> CONSTRAINT FKSN_REL_CONTRATO_PRODUTO
> FOREIGN KEY (NUM_CONTRATO,
> CID_CONTRATO)
> REFERENCES SN_CONTRATO (
> NUM_CONTRATO,
> CID_CONTRATO)
>)
>
>
>The information from the oracle data dictionary could be returned like this:
>FN_NAME TABLE_NAME COLUMN REFERENCED_TABLE_NAME REFERENCED_TABLE_COLUMN
>
>Thanks for any help:
below is a script that, when given the name of a PARENT table, will list all of
the foreign key references made by child tables. this is useful for example if
you wish to export the parent table, drop it with cascade constraints, import it
and then re-enable all of the child constraints again. For example, given a
schema like:
SQL> create table p ( x int, y int, z int, data int,
2 primary key(x,y,z) );
Table created.
SQL>
SQL> create table c1 ( a int primary key, c1x int, c1y int, c1z int, data int,
2 foreign key(c1x,c1y,c1z) references p(x,y,z) );
Table created.
SQL>
SQL> create table c2 ( a int primary key, c2x int, c2y int, c2z int, data int,
2 foreign key(c2x,c2y,c2z) references p(x,y,z) );
Table created.
I can then run:
SQL> @cons p
old 53: and parent.parent_tname = upper('&1')
new 53: and parent.parent_tname = upper('p')
FKEY
alter table "C1"
add constraint "SYS_C0032209"
foreign key ( "C1X", "C1Y", "C1Z" )
references "P" ( "X", "Y", "Z");
alter table "C2"
add constraint "SYS_C0032211"
foreign key ( "C2X", "C2Y", "C2Z" )
references "P" ( "X", "Y", "Z");
If you spool that and save it, you will get back all of your foreign keys.
- cons.sql --------------------------------------
column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||column_name||'"',NULL)) ||
max(decode(position, 2,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 3,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 4,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 5,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 6,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 7,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 8,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 9,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,10,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,11,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,12,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,13,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,14,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,15,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,16,', '||'"'||column_name||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||column_name||'"',NULL)) ||
max(decode(position, 2,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 3,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 4,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 5,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 6,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 7,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 8,', '||'"'||column_name||'"',NULL)) ||
max(decode(position, 9,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,10,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,11,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,12,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,13,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,14,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,15,', '||'"'||column_name||'"',NULL)) ||
max(decode(position,16,', '||'"'||column_name||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/
- eof -------------------------------------------
>
>Cesta
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Mon Dec 14 1998 - 07:30:22 CST
