topic Re: Broken Foreign Keys in Operating System - HP-UX

Broken Foreign Keys

Chassidy Rentz — Thu, 16 Jun 2005 13:46:36 GMT

I made an export of a database with no broken foreign keys and ftp'd it to another server where I performed a re-org. This imported the clean database. Sometime between yesterday and today one of the foreign keys broke, but no one has been on the system and all objects are valid. What could have caused this foreign key to break?

Re: Broken Foreign Keys

TwoProc — Thu, 16 Jun 2005 16:32:16 GMT

Is the primary key (that the foreign key points to) possibly a key field that's populated from a sequencer that's triggered on row insert? If so, you're import may have fired the trigger and generated a whole new set of primary keys , hence your foreign key is pointing to nothing. Have to be careful to turn off triggers before starting imports because of this very thing. This may not be your problem, but in general it's what I look for in the very scenario you've described.

Re: Broken Foreign Keys

Eric Antunes — Fri, 17 Jun 2005 04:12:54 GMT

Hi,

Before import, disable all triggers executing the result of the following query:

select 'alter trigger'||' '||owner||'.'||object_name||' '||'disable'
from dba_objects
where object_type = 'TRIGGER';

And, after, re-enable them:

select 'alter trigger'||' '||owner||'.'||object_name||' '||'enable'
from dba_objects
where object_type = 'TRIGGER';

Regards,

Eric Antunes

Re: Broken Foreign Keys

Fred Ruffet — Fri, 17 Jun 2005 05:35:52 GMT

If the DB your importing to does not have the schema, you'll have to import first without data, disable triggers, import data, and then reenable triggers.

Here is the sequence I used (it concern a fromuser/touser import, not full) :

1. import tables structure with options "rows=n indexes=n constraints=n grants=n" and some other extra options such as buffer, ignore...

2. disable trigger with a script like that :
sqlplus /nolog << EOF
connect ${login}/${pass}
set verify off
set head off
set pages 0
spool trigoff_$$.sql
select 'alter trigger '||trigger_name||' disable;' from user_triggers;
spool off
sta trigoff_$$.sql
exit
EOF
rm trigoff_$$.sql

3. import data with a new imp command with options "indexes=n constraints=n grants=n".

4. enable constraints with a last import command using options "rows=n indexes=y constraints=y grants=y"

Import data without indexes and constraints make it faster than all-in-one. You have to parse 3 times the file so it may be a better thing to have a user export than a full export.
Triggers will be activated by imp itself at end of step 3.

Regards,

Fred

Re: Broken Foreign Keys

Yogeeraj_1 — Fri, 17 Jun 2005 08:56:07 GMT

hi,
What do you mean by "broken"?

what is the status of the constraint?

query the status field:
desc dba_constraints
Name Null? Type
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE

let us know

regards
yogeeraj