Oracle Database Discussions

Hi,

I have below table.

CREATE TABLE unique_date_check
(employee_id NUMBER ,
amount NUMBER ,
pay_date DATE DEFAULT sysdate ,
CONSTRAINT uq_pay_date UNIQUE (pay_date));


The table should accept only one amount per employee per pay_date.

I am using sysdate as pay_date. Since sysdate has also timestamp in it, the constraint is checking for uniqueness both on date and timestamp.

I need uniqueness only on the date part (i.e., mm/dd/yyyy), but not on timestamp.

Do I have use triggers for this or is there any other way to meet my requirement?


Thanks in advance.
Dinakar