[Oracle] Partition Key column not part of Primary Key columns

[sanyamsinghal]

In YugabyteDB, If the table is partitioned on a column then that column needs to be a part of the PRIMARY KEY columns.

For example, Exported Schema:

CREATE TABLE employees (
employee_id integer NOT NULL,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date timestamp DEFAULT statement_timestamp(),
job_id varchar(10),
salary double precision,
part_name varchar(25),
PRIMARY KEY (employee_id)) PARTITION BY RANGE (hire_date) ;

YugabyteDB will throw this error:

ERROR:  insufficient columns in the PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "employees" lacks column "hire_date" which is part of the partition key.

Workaround: Add all Partition columns to Primary Key

CREATE TABLE employees (
employee_id integer NOT NULL,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date timestamp DEFAULT statement_timestamp(),
job_id varchar(10),
salary double precision,
part_name varchar(25),
PRIMARY KEY (employee_id, hire_date)) PARTITION BY RANGE (hire_date) ;