I have a SQL table like below.
CREATE TABLE my_table
(
id serial,
user_id int,
order_no character varying(50),
date_time timestamp,
primary key (id, user_id, date_time),
unique(user_id, order_no)
) PARTITION BY RANGE (date_time);
I have a table. I need to unique order_no column with user_id column. there is no any problem.
But I have to unique data_time column because of I use partition. but date_time column has to be separated with user_id and order_no.
By the way I don't want like this unique (user_id, order_no, data_time)
How can I unique date_time in this table ?
There is no way to create a unique index or constraint that guarantees that order_no or user_id are unique. That is one of the drawbacks of partitioning.
You could create unique constraints on the partitions. While that doesn't guarantee global uniqueness, it goes a long way.