How to TRUNC ???

From: Yuval Sverdlik <ysverdlik_at_nj.rr.com>
Date: Mon, 15 Oct 2001 23:51:17 GMT
Message-ID: <VDKy7.13196$T5.2327301@typhoon.nyc.rr.com>

I have a table that I'm creating. (a customer table) I need to figure out how to get a constraint where a customer can have only one order per day. Here is the table below. I know that if I TRUNC the date it will make the time portion of the date to 12:00am and therefore if sysdate is used the date will still be the same throughout the day.

Now if I use sysdate I can insert two of the same customers with sysdate because the date is really not the same even if i did it at the same time.

Please help this beginner:

Thanks

CREATE table Ordr_Data
(
Order_id NUMBER(10) CONSTRAINT pk_order_data_order_id PRIMARY KEY, Cust_id NUMBER(5) NOT NULL,
Sales_rep_id NUMBER(5) NOT NULL,
Prod_id NUMBER(10) NOT NULL CONSTRAINT uq_ordr_data_prod_id UNIQUE, Quantity NUMBER NOT NULL CONSTRAINT ck_quantity CHECK(Quantity <= 1000), Order_date DATE NOT NULL CONSTRAINT uq_order_date UNIQUE, Order_Stat VARCHAR2(10) NOT NULL,
Ship_Date DATE NOT NULL,

Org_User_id NUMBER(5) NOT NULL,
Org_User_Date DATE  NOT NULL,
Upd_User_id NUMBER(5),
Upd_User_Date DATE,

CONSTRAINT fk_ordr_data_cust_id FOREIGN KEY (Cust_id) REFERENCES Cust_Data, CONSTRAINT fk_ordr_data_Sales_rep_id FOREIGN KEY (Sales_rep_id) REFERENCES Emp_data,

CONSTRAINT fk_ordr_data_Prod_id FOREIGN KEY (Prod_id) REFERENCES Prod_data,
CONSTRAINT uq_Ordr_Data_Custid_ordrdt UNIQUE (cust_id,Order_date),
CONSTRAINT ck_Order_date CHECK(order_date>

to_date('01/01/2001','dd/mm/yyyy'))); Received on Mon Oct 15 2001 - 18:51:17 CDT