How to generate FK constraints inside table

Toad Data Modeler
1

I’d like to use Oracle 11g’s reference partitioning feature. This requires that the FK constraint in the child table be specified in the create table statement. Is there a way to do this as with PK constraints?

Here’s an example of the DDL I need to generate

CREATE table order_items
( order_id NUMBER(12) not null,
line_item_id NUMBER(3) not null,
CONSTRAINT order_items_fk FOREIGN KEY REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

I can put the PARTITION BY REFERENCE(order_items_fk) in the table or physical properties section but unless the FK constraint is generated with the create table statement this won’t work.

Any suggestions?

Thanks,
Paul

2

Hi Paul,

Thanks for your post. I need to consult it with our expert who, unfortunately, is out of office for a couple of days. As soon as I know more, I’ll write you immediately.

Thanks a lot for your patience!

Regards,

Vladka

3

Hi Paul,

Unfortunately, we can’t offer you an immediate solution.
We will deal with this issue. I’ve created a new CR for it. CR # 66 580.

Thanks.

Regards,

Vladka + TDM Team

4

OK, Thanks. Is there some way I can track the CR?

5

Hello,

The CRs are listed in our internal system. However, you can ask about the progress of the CRs here in the community.

All resolved CRs are listed in the Release Notes document of the Beta and commercial TDM.

If you have any questions, please write me back. Thanks.

Regards,

Vladka

6

Do you have an ETA for this enhancement?

Thanks,
Paul

7

Hi Paul,

We are dealing with this issue right now.
We keep failing to reproduce the problem in TDM v 3.5.14.

Paul, please write us what TDM version you use.
Also, please verify whether statement “PARTITION BY REFERENCE(order_items_fk)” is in Table Properties (NOT in Physical Properties).
If you do have it in Table Properties, we’d like to ask you to send us your model to modeling@quest.com.

We will check it out.

Thanks a lot!

Regards,

Vladka + TDM Team

8

Vladka,

You are correct – it works properly now. I was confused because
normally the FK constraint is defined after the create table statement.
But, TDM is smart enough to move it up if the table properties
references it.

Thanks for your help. You can close this ticket.

Paul

9

Hi Paul,

Perfect. Thanks for your confirmation.

Regards,

Vladka