Orphaned Records 2

[bobby13]

My Previous query was:
I'm trying to create a Constraint named FK_ANALYSER_COLUMNS_ANALYSER
for table ANALYSER_COLUMNS

the SQL query is:
alter table ANALYSER_VALUES add constraint FK_ANALYSER_VALUES_ANALYSER foreign key (ANALYSERID) references ANALYSER (ANALYSERID)

Result:
ERROR at line 1:
ORA-02298: cannot enable (EMSMAIN.FK_ANALYSER_VALUES_ANALYSER) - parent keys
not found


To resolve the problem I ran the following SQL statement as advised on the database and no rows where returned:

SQL> select analyserid from analyser_values
2 minus
3 select analyserid from analyser;

no rows selected

Do we need to add a selection criteria to the SQL statement?

 

[carp]

Let's back up and double check my assumptions.

Is ANALYSER_VALUES the parent or the child table?

 

[bobby13]

Hi carp,

the table analyzer_values is the child table see below the attributes:

ANALYSER_COLUMN_ID NOT NULL NUMBER(10)
ANALYSER_ROW_ID NOT NULL NUMBER(10)
ANALYSERID NUMBER(10)
ROW_ORDER NUMBER(10)
COLUMN_ORDER NUMBER(10)
VALUE VARCHAR2(100)

The parent table is Analyser:

ANALYSERID NOT NULL NUMBER(10)
ANALYSER_NAME VARCHAR2(30)
USE_BY DATE
USER_ID NUMBER(10)
DEFAULT_ENTRY_DATE DATE
LAST_SAVE_DATE DATE
LAST_PRINT_DATE DATE
ANALYSER_MEMO_ID NUMBER(10)
DEFAULT_FROM_DATE DATE
DEFAULT_TO_DATE DATE
SQL_STRING LONG
STUDENT_FROM_DATE DATE
STUDENT_TO_DATE DATE

So i want to make analyserid the foreign key in the table analyser_values. Thanks for your help in advance!

PS: Are you located in the US or europe, i'm in UK!

 

[carp]

Bobby13 -
I'm in the US - Colorado, to be precise.

OK - so the syntax is correct, there don't appear to be orphaned records, yet you're getting this error that says there are.

You might try this:
Create the constraint in a disabled state:

alter table ANALYSER_VALUES add constraint FK_ANALYSER_VALUES_ANALYSER foreign key (ANALYSERID) references ANALYSER (ANALYSERID) DISABLE;

Then make sure you have the EXCEPTIONS table defined. If you don't, then run the utlexcpt.sql script to create it.

Now try to enable the constraint:

ALTER TABLE analyser_values
ENABLE CONSTRAINT
fk_analyser_values_analyser
EXCEPTIONS INTO EXCEPTIONS;

You will still get the error message - but Oracle will also write a record to the EXCEPTIONS table for every orphan in your child table. This will allow you to identify/correct the problem child(ren).

 

[deveshjogal]

Bobby

Can u tell me is ANALYSERID is primary key or unique key in
analyser table?

devesh

 

[carp]

It must be - otherwise, you would get a different error indicating that the parent didn't have a Unique or PK constraint.

 

[bobby13]

The analyserid is a primary key and not a unique key for the table analyser. I created the exceptions table and got a list of 54 rows of orphaned records looking like this:

ROW ID OWNER TABLE_NAME
-------- ------ -----------
AAABoBAALAACFhCAAB TESTDB ANALYSER_COLUMNS

CONSTRAINT
------------
FK_ANALYSER_COLUMNS_ANALYSER

How do i delete this records, should i just drop the table exceptions and this will delete all records from database!

Thanks for you help guys!

PS: CARP the reason why i wa asking about your location was, there seemed to be a time lag between when i post a message and get responses. So when you guys in the US start work I'm logging off to go HOME!

 

[sem]

Can you look what ANALYSERIDs are on those rowids? It's very strange, because the query returned NO orphaned records.

Regards, Dima

 

[bobby13]

How do i check this Dima:

Can you look what ANALYSERIDs are on those rowids? It's very strange, because the query returned NO orphaned records.

Sorry i'm just a novice, the world of Oracle i still new to me.

 

[sem]

Ok, now I understand your REAL problem. Please, read ALL your messages again. So, are you creating foreign key for ANALYSER_COLUMNS or ANALYSER_VALUES?????? You looked for orhans in the table, that really didn't contain them!

Regards, Dima

 

[bobby13]

My mistake the table i'm trying to the FK on is called ANALYSER_COLUMNS and not ANALYSER_VALUES, I have done all the above as advised by carp and the rest on ANALYSER_COLUMNS and not ANALYSER_VALUES. The situation is still the same - parent key not found!

Regards
Bobby

 

[sem]

Try this one:

select c.ANALYSERID
from EXCEPTIONS e, ANALYSER_COLUMNS c
where e.table_name = 'ANALYSER_COLUMNS'
and c.rowid = e.row_id

Regards, Dima

 

[bobby13]

Hi Dima,

I ran the script and it returned 54 rows, displaying the analyserid:

ANALYSERID
----------
14

ANALYSERID
----------
18

and so on....

I have checked analyserid 14 against table analyser and found that analyserid 14 does not exist.

In the table ANALYSER there exists only one analyserid namely ID number 34, so all the other id's are orphaned records - is this assumption correct?

Regards
Bobby

 

[sem]

And

select analyserid from ANALYSER_COLUMNS
minus
select analyserid from ANALYSER

returns no rows????

Regards, Dima

 

[bobby13]

thanks dima i've now resolved the problem! I shall now try doing the same for the other two tables, hopefully it shall be smooth sailing.

Once again thank you all for your contributions!

Regards
Bobby