I am trying to import from one user (TEST) to another (TEST2). Everything
works okay, except for the following error msg received when trying to
import foreign keys:
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "ACTIONPROPERTY" ADD CONSTRAINT "FKACTION_ACTIONPROPERTY"
"GN KEY ("ACTIONID", "ACTIONVERSIONID") REFERENCES "ACTION" ("ACTIONID",
"TIONVERSIONID") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ENABLE
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
I get this for all foreign keys that it tries to import. What really puzzles
me is that I did a query to see if the primary keys had been imported to the
TEST2 schema and they are all there. Any ideas as to what is going on?
Thanks in advance for any help you can provide!
First thought is the create sequence. The foreign key constraint in table A points to a column int table B, but table B hasn't been created yet.
I would generate a SQL script to create all the tables without the constraints, then import the data, then add the constraints.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Is there a relatively easy way to generate the SQL to create the tables w/out constraints?
first step is to import the tables without indexes and constraints.
second is to generate a script using the import utility and the export file (dmp) containing all the constraints and indexes creation statements.
and at last execute them.
8i OCP DBA
OK....still having problems. This is what I've done:
1. Run import with indexfiles so that it creates a script for creating the indexes and constraints.
2. Run import for tables only....set indexes=N and constraints=N.
3. Run indexfile script in SQL*Plus to create indexes and enable constraints.
Result: I received the same error as in my original post, plus I am also receiving ORA -02430: cannot enable constraint (constraint_name) - no such constraint.
I understand why I receive the ORA-02430 on foreign keys - they are not being created because of ORA-02270 msg reported in the original post. But, I do not understand why I am receiving the ORA-02270, or the ORA-02430 for primary keys. When I query dba_indexes all primary keys are there and have a status of VALID.
I am STILL struggling with this one.....it may be elementary, but, if it is, I have sure missed something. In addition to above, I have checked permissions on user doing import and all checks out there.
Does anyone have any ideas that could help me out with this?
Thanks in advance for any help at all!
do you think the source data is not a proper one.
like, in ur source data were the constraints were disalbled - manupulated - Enabled with NOVALIDATE ?
If so u may have to do the same and fix it later.
Click Here to Expand Forum to Full Width