Re: Table/column constraints

In article <20v3h0$4ah_at_gdls.gdls.com> mccabe_at_gdls.com (Harold McCabe) writes:
>Oracle SQL Reference Manual says that table and column constraint
>information ends up in CONSTRAINT_DEFS and CONSTRAINT_COLUMNS. This
>seems to work OK for the one and only column constraint supported in
>v 6: NOT NULL. But if I have something like:
>
> create table test1 (value number(2) not null,
> primary key (value))
>
> create table test2 (value number(2) not null,
> fk number(2), foreign key (fk) references
> test1)
>
>and I try to drop table test1 *BEFORE* I drop table test2 I see
>
> ORA-02273: this unique/primary key is referenced by some foreign keys
>
>Where is this information stored?? It does not show-up in CONSTRAINT_DEFS,
>CONSTRAINT_COLUMNS, or CON$.
>
>Harold McCabe
>mccabe_at_gdls.com
>

Here is the SQL to find the foreign keys in a table call ACMSREL:

  1 select c.column_name "LocalCol", a.table_name "LocalTab", d.column_name "ForeignCol", b.table_name "ForeignTab"   2 from constraint_defs a, constraint_defs b, constraint_columns c, constraint_columns d   3 where a.r_constraint_name = b.constraint_name

  4    and a.constraint_name = c.constraint_name
  5    and b.constraint_name = d.constraint_name
  6    and a.table_name = 'ACMSREL'

LocalCol                       LocalTab
------------------------------ ------------------------------
ForeignCol                     ForeignTab
------------------------------ ------------------------------
LOCATION                       ACMSREL
LOCATION                       RFLOCATIONS

OUTAGE_CODE                    ACMSREL
OUTAGE_CODE                    RFOUTAGES
-- 
Gary Hall                  | Voice (604) 291-3208 | INTERNET: hall_at_cs.sfu.ca
Centre for Systems Science | Fax   (604) 291-4424 | 
Simon Fraser University    | 			  
Burnaby, B.C.  V5A 1S6     |