Unique constraint based on two columns, oracle

Question

Can someone help me with unique constraint? I have table:

create table vote
(
    ID            NUMBER(19) not null primary key,
    USER_ID       NUMBER(19),
    DATE          TIMESTAMP(6)
);

I need USER_ID and DATE to be unique only in case USER_ID is unique and DATE is unique for particular day. Let user to insert in other cases. For example:

INSERT INTO vote (ID, USER_ID, DATE) values (1, 1, 2023-01-01 10:01:15);
-true
INSERT INTO vote (ID, USER_ID, DATE) values (1, 1, 2023-01-02 23:11:00);
-true
INSERT INTO vote (ID, USER_ID, DATE) values (1, 1, 2023-01-01 12:55:12);
-false
INSERT INTO vote (ID, USER_ID, DATE) values (1, 2, 2023-01-01 03:35:55);
-true
INSERT INTO vote (ID, USER_ID, DATE) values (1, 2, 2023-01-02 06:40:29);
-true
INSERT INTO vote (ID, USER_ID, DATE) values (1, 2, 2023-01-02 18:01:42);
-false

In other words DATE must be unique only for year, month, day (yyyy-mm-dd). Hours, minutes and etc.. must not be considered.

Answer 1

Create a unique index on user_id and TRUNC("DATE"):

create table vote
(
    ID            NUMBER(19) not null primary key,
    USER_ID       NUMBER(19),
    "DATE"        TIMESTAMP(6)
);

CREATE UNIQUE INDEX vote__user_id__date__u ON vote (user_id, TRUNC("DATE"));

Then:

INSERT INTO vote (ID, USER_ID, "DATE") values (1, 1, TIMESTAMP '2023-01-01 10:01:15');
INSERT INTO vote (ID, USER_ID, "DATE") values (2, 1, TIMESTAMP '2023-01-02 23:11:00');
INSERT INTO vote (ID, USER_ID, "DATE") values (3, 2, TIMESTAMP '2023-01-01 03:35:55');
INSERT INTO vote (ID, USER_ID, "DATE") values (4, 2, TIMESTAMP '2023-01-02 06:40:29');

succeeds.

But:

INSERT INTO vote (ID, USER_ID, "DATE") values (5, 1, TIMESTAMP '2023-01-01 12:55:12');

and:

INSERT INTO vote (ID, USER_ID, "DATE") values (6, 2, TIMESTAMP '2023-01-02 18:01:42');

Both fail with the error:

ORA-00001: unique constraint (FIDDLE_IQTGCWJPTRJRQFSLCDHX.VOTE__USER_ID__DATE__U) violated

fiddle