How can multiple unique in partition tabale?

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 ?

Answer

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.