YugabyteDB now supports foreign key references to partitioned tables…a PostgreSQL 12+ feature that brings referential integrity to partitioned parents.
This is available starting in v2.25 (Preview) and fully GA in v2025.1 (STS).
Why It Matters
Partitioning is powerful for managing large, rapidly changing datasets. It helps with:
• Lifecycle management: Easily drop old data via partitions instead of running expensive
DELETEs• SHARDING plus geo/multi-tenant control: A way to organize and isolate data effectively
Until now, YugabyteDB supported foreign keys only when the parent was a regular table. With this change:
• You can now reliably enforce referential integrity on partitioned parent tables
• This enables safe cascades, constraint checks, and joins on partitioned schemas… just like with PostgreSQL’s native functionality
Quick Example
-- 1. Create a partitioned parent table
CREATE TABLE customer_metrics (
customer_id INT,
metric_date DATE,
metric_value NUMERIC,
PRIMARY KEY (customer_id, metric_date)
)
PARTITION BY RANGE (metric_date);
CREATE TABLE customer_metrics_2025_01 PARTITION OF customer_metrics FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE customer_metrics_2025_02 PARTITION OF customer_metrics FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- 2. Create a child table referencing the partitioned parent
CREATE TABLE customer_reports (
report_id SERIAL PRIMARY KEY,
customer_id INT,
metric_date DATE,
report_text TEXT,
FOREIGN KEY (customer_id, metric_date)
REFERENCES customer_metrics (customer_id, metric_date)
) ;
Before v2.25, this would raise an error – foreign keys could not reference a partitioned table.
yugabyte=# CREATE TABLE customer_reports (
yugabyte(# report_id SERIAL PRIMARY KEY,
yugabyte(# customer_id INT,
yugabyte(# metric_date DATE,
yugabyte(# report_text TEXT,
yugabyte(# FOREIGN KEY (customer_id, metric_date)
yugabyte(# REFERENCES customer_metrics (customer_id, metric_date)
yugabyte(# ) ;
ERROR: cannot reference partitioned table "customer_metrics"
Now, it works perfectly and maintains referential integrity across the partitioned parent table!
yugabyte=# CREATE TABLE customer_reports (
yugabyte(# report_id SERIAL PRIMARY KEY,
yugabyte(# customer_id INT,
yugabyte(# metric_date DATE,
yugabyte(# report_text TEXT,
yugabyte(# FOREIGN KEY (customer_id, metric_date)
yugabyte(# REFERENCES customer_metrics (customer_id, metric_date)
yugabyte(# ) ;
CREATE TABLE
Why This Is a Big Deal
• Simplifies schema design… you no longer need a flat parent table just to satisfy foreign keys
• Makes partitioning more practical for multi-tenant or time-series workloads
• Enables cleaner data lifecycle patterns with safe cascade deletes and constraint checks
• Fully compatible with PostgreSQL’s partitioning/foreign keys semantics
Have Fun!
