CODEWITHSUNDEEP
intellij-ideaoracle-sqldeveloperpartitioningdatabase-partitioningoracle19c
an edith
an edith

Reputation: 1

How to Partition by Reference in Oracle 19c IDE (Intellij)?

Is "partition by reference" not supported in Oracle 19c?

For some reason, my IDE (Intellij) keeps complaining when I try to partition by reference when creating a table, saying: " HASH, LIST, RANGE, SQL_REFERENCE or SYSTEM expected, got 'REFERENCE' "

I checked the Oracle version, this is Oracle 19c.

I tried partition by list, this works.

I tried creating the parent table and the child table both with the primary and foreign key defined as table constraints instead of column constraint. Does not work.

I tried the example I found here:

CREATE TABLE orders (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE NOT NULL,
  customer_id NUMBER NOT NULL,
  shipper_id  NUMBER)
PARTITION BY RANGE (order_date) (
  PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
  PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
  PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));

CREATE TABLE order_items (
  order_id    NUMBER NOT NULL,
  product_id  NUMBER NOT NULL,
  price       NUMBER,
  quantity    NUMBER,
  CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION BY REFERENCE (order_items_fk);

This does not work either...

But is DOES work in Oracle SQL Developer!

What is causing this? Probably some very stupid mistake? (I hope)

Upvotes: 0

Views: 239

Answers (1)

M.H.Contractor
M.H.Contractor

Reputation: 1

Partition by reference is based on the primary constraint of parent table and foreign key constraint of child table. The partition key should be primary key column. In your example you are portioning by date column which does not have primary key.

Upvotes: 0

Related Questions