Re: Constraint Confusion

Disabling fk constraints isn't enough. You need to drop and recreate the foregn key constraints. I ran into this same problem trying to reorganize a table recently. I though I could disable all the FKs referencing it, exp/imp the table, then reenable the constraints. I thought wrong!

Here's a procedure you could try. Export the DDL for the tables involved, drop the FK constraints, modify the column definitions, and recreate the FK constraints with an import.

exp userid=schema/password compress=n rows=n tables="(parent,child1,child2,...)"

sqlplus schema/password @modify.sql

imp userid=schema/password full=y

You may need to include the ignore=y option on the import. I haven't really tried this.

On Sun, 21 Jun 1998 19:00:31 GMT, doug <dcowles_at_bigfoot.com> wrote:

>I recently was told that some DDL sitting under an application was
>designed incorrectly,
>and that several CHAR(12) fields should be changed to numeric fields.
>Three tables
>were directly affected by this decision, and 4 more had foreign keys of
>the wrong type
>into these tables. I first did a disable with the cascade option on
>every table in the schema, and then successfuly altered the type of one
>field to numeric. But when
>I tried the child table, it said it could not perform this action with
>current constraints.
>A select on user_dependencies revealed nothing, and no constraints were
>enabled.
>I could not even drop this table without getting the same constraint
>error. I am
>very confused....If anyone has any thoughts, please let me know. I'm
>using Oracle 7.1.6
>by the way.
>Thanks in advance....
>dcowles_at_bigfoot.com

--
Chuck Hamilton
chuckh_at_dvol.com

If at first you don't succeed, sky diving isn't for you. Received on Tue Jun 23 1998 - 13:31:12 CDT