Cannot remote ODI Datastore because the table has a PK referenced by other tables' FKs

3673816

Hello.

I have a datastore for a dimension table that has a PK that is being referenced by other tables' FKs. There are datastores for those other tables, too.

I need to drop and recreate the dimension table's datastore. However, when I do that, I get:

ODI-10090: Object Datastore : STU.ORA_STU_TRG.Global.HANDSHAKE_EMPLOYER_D is referenced by:

Reference :STU.ORA_STU_TRG.Global.HANDSHAKE_INTERVIEW_F.HANDSHAKE_INTERVIEW_F_FK02,

Reference :STU.ORA_STU_TRG.Global.HANDSHAKE_EVENT_ATNDE_F.HANDSHAKE_EVENT_ATNDE_F_FK02.

Cannot remove.

No matter if I have the other tables' FKs disabled in the database. I still get the error.

What is the "Reference" that ODI is alluding to? Is there a way to disable it, so that this datastore can be dropped and recreated?

The only way I can make this work is go and remove the other tables' mappings and datastores before this datastore can be removed, but that's silly.

Thanks

Boris

Adrian_Popescu-Oracle

Hi Boris,

You may try to drop the FK constraints in the datastores, at ODI level (your datastore \ Constraints).

Then drop your datastore

These FKs will be recreated when you reverse engineer the model.

Adrian

Adrian_Popescu-Oracle

Hi Boris,

You may try to drop the FK constraints in the datastores, at ODI level (your datastore \ Constraints).

Then drop your datastore

These FKs will be recreated when you reverse engineer the model.

Adrian

ashud1

Hi,

Just to add the more on the understanding part.

When we enable the FK relationship on Database and Reverse that in ODI, Then this relationship also being pulled with it. Now Even if you Drop the constraints on Database level , ODI still has that relationship . As  Adrian mentioned above you can delete the constraints or Simple Reverse the datastore one more time.

Thanks

3673816

Thank you Adrian and Ashud1.

How does one go about deleting a constraint in the ODI datastore?

I figured ODI keeps the relationship internally, based on the error that I got. My issue was that I couldn't find where that relationship is being kept. I don't see any tabs or sections in my datastore window that says "constraints" or anything like that.

Thanks

Boris

Adrian_Popescu-Oracle

Hi Boris,

See constraints here:

3673816

Thank you Adrian.

I must have stared at it for at least a few minutes yesterday and didn't see it. :-) It all makes sense now.