This content has been marked as final. Show 14 replies
I have around 38000 rows in the question_bank table and it has its child records in other tables. But now after I created it with import utility the child tables does not accept record.
I have many other tables with master-child relationship but they are functioning fine after the import. It is only with this table question_bank.
>>the child tables does not accept record.
You should confirm what are the foreign key constraints defined in that child tables that are causing this "problem" as Yingkuan said. In addition, you can use this SQL below:
select r.owner, r.table_name,r.constraint_nameCheers
user_constraints r, user_constraints o
r.r_owner = o.owner and r.r_constraint_name = o.constraint_name
and o.constraint_type in ('P','U') and r.constraint_type = 'R'
and o.table_name = 'QUESTION_BANK';
I did check the foreign key constraints and found to be ok. The result of one of the foreign key for the suggested sql statement is
OWNER TABLE_NAME CONSTRAINT_NAME
TUTOR QUESTION_CHOICES QUE_CHOICE_QB_ID_QUE_BANK_R
As I have mentioned earlier I have already recreated the primary key and foreign key but the problem still persists. Should I try exporting the table 'question_bank' and importing it again.
I dont think that would solve your problem. Whetever oracle is suggesting is correct. While importing rows into child table it checks for constraints. If data is important for you then you can disable all constraints and then import. Afterwards you can crosscheck if you are able to enforce the constraints.
I suspect the issue in question_bank table as none of the child table is allowing the record to be inserted due to foreign key constraint failing.
I wish to take the following steps:
a.Truncate table Question_bank.
b.Export table question_bank from source database.
c.Import table question_bank into target database.
What do you say?
I can not imagine about the data you are dealing with, your problem is suggesting that question_bank table doesnot have specific rows needed by reffering tables. As I said you can disable constraint(s) and then import child tables. How will your method change the scenario...There might be one condition in which you may have deleted rows from question_bank table.
I identified the issue and resolved it. The problem was in the associated index to the primary key. I have many indexes on question_bank table. There was a nonunique index cola_colb_idx on colA (primary key column) and colB. Also there was a unique index cola_idx on colA.
Now when I imported the table the primary key got associated with cola_colb_idx, which was a nonunique index on two columns.
I dropped the table and imported the table without the indexes and constraints and manually created the indexes and constraints. This resolved the issue.
I have a question now that how to identify which index is associated with primary key?
Thank you for the patience and suggestions.
>>I have a question now that how to identify which index is associated with primary key?
LEGATTI@XE> create table t (id number constraint pk_t primary key);Cheers
LEGATTI@XE> select constraint_name,index_name from user_constraints where table_name='T';