RE: Missing foreign key - Oracle Note for 8i

From: David Barbour <DBarbour_at_connectsouth.com>
Date: Tue, 18 Jul 2000 12:58:41 -0500
Message-Id: <10562.112299@fatcity.com>

This note may provide some insight. The IGNORE=Y doesn't always work.

Subject: IMP-17, ORA-2261 ENCOUNTERED ON IMPORT

Problem Description:

You are running two imports - the first imports table structure, and the second imports data. This is a solution used to recreate an entire database

but only import data into some of the tables. You receive the following errors:

IMP-17: following statement failed with error 2261 ORA-2261: such unique or primary key already exists in the table

The statement returned is an ALTER TABLE ADD CONSTRAINT statement.

This may be followed by:

ORA-2264: <name> already used by an existing constraint

Problem Explanation:

The ALTER TABLE ADD CONSTRAINT statement is not ignored with IGNORE=Y parameter
of Import. The unique or primary constraints cannot be recreated on the second
import after being created on the first, structure-only import.

You then set the parameter for the first export with CONSTRAINTS=N, so that the
constraints are not built until the second export. When you run these imports,
you recieve error:

ORA-2298: cannot enable constraint (%s.%s) - parent keys not found

This error is reported because you have tables with parent keys that are being
imported after tables with child records, so the child record looks for the parent key, but it does not exist yet and so it fails.

Search Words:

imp-3, parent key not found, ora-2294, ora-2292, ora-2291, Oracle Utilities,

import, export

Solution: RUN STRUCTURAL EXPORT WITH CONSTRAINTS=Y AND DATA EXPORT WITH CONSTRAINTS=N. Solution Description:

You will have to run the structural export with CONSTRAINTS=Y and the data export with CONSTRAINTS=N. Follow these steps:

Run the first export with parameter ROWS=N and parameter CONSTRAINTS=Y.
Run the second export with ROWS=Y and CONSTRAINTS=N.
Run the first import to build the tables with constraints.
Disable all foreign key constraints using ALTER TABLE DISABLE CONSTRAINT

<constraint name>.

5. Run the second import that imports data without building constraints.

6. Enable all foreign key constraints using ALTER TABLE ENABLE CONSTRAINT <constraint name>.

Solution Explanation:

This solution will allow you to make two import runs of the same tables, one

with structure only and one with data. Because ORA-2298 will stop us if we attempt to import tables with constraints, we can import the constraints on the
structure import, then disable all foreign key constraints temporarily while we
import data. After data is imported, we can enable all foreign key constraints.
.

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
From: Cale, Rick T (Richard) [mailto:RICHARD.T.CALE_at_saic.com] Sent: Tuesday, July 18, 2000 10:14 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Missing foreign key

Is it possible that the constraint was disabled then data was added then the constraint was
enabled with the ENFORCE option which I think only does RI constraints for all future data
not existing data. You may want to check the dba_constraints table to see what constraints
are enabled and how.

Rick

> -----Original Message-----
> From: Chuck Hamilton [SMTP:chuck_hamilton_at_yahoo.com]
> Sent: Tuesday, July 18, 2000 9:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Missing foreign key
>
> Anyone ever encounter a situation where you've had missing parent keys but
> the foreign key constraint is still enabled? What caused it? We have that
> situation right now on an 8i database.
>
>
>
>
>
> _____
>
> Do You Yahoo!?
> Get Yahoo! Mail <http://mail.yahoo.com/> - Free email you can access from
> anywhere!

-- 
Author: Cale, Rick T (Richard)
  INET: RICHARD.T.CALE_at_saic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Tue Jul 18 2000 - 12:58:41 CDT