I'm creating a table that should check various date constraint, but Oracle is kind ticky on this argument. My goal is to reserve a certain room for let's say a lecture, so I thought of something like this:
.... prenotation_date date NOT NULL, starting_date date NOT NULL, ending_date date NOT NULL,
check (/strarting_date > prenotation_date+3gg/ ) -> you can book a room only 3 days after the time you're asking the reservation
check (/* starting_date = ending_date */ ) in regards of the date you're booking, meaning you can book a room for a period of one day
check (/* starting_date < ending_date */) in regards of the time you're considering, meaning that the time in witch you start to use the room must be earlier of the time you intend of leaving the room (let's say you want to reserve it from 8:00 a.m. to 10:00 a.m.
the problem is that I don't know how consider just a portion of the data, but I guess there's a way to accomplish that, otherwise you should create 4 columns instead of 2 and use for a couple a dummy date and for the other a dummy time, which seems clunky to me. So I guess the question is, how do I consider the date type in its single components? Is that even possible? thanks for the attention and sorry for the long post.
You could use a CHECK constraint with following conditions:
starting_date > prenotation_date + 3 will make sure that the booking is allowed after 3 days of the reservation.
starting_date < ending_date will make sure that the time at which a booking is made is always before to the leaving time.
TRUNC(starting_date) = TRUNC(ending_date) will make sure that the booking is done for the same day. That is the booking window is only for one single day.
Test case:
CREATE TABLE
SQL> CREATE TABLE t(
2 prenotation_date DATE NOT NULL, starting_date DATE NOT NULL, ending_date DATE NOT NULL);
Table created.
SQL>
ADD CHECK CONSTRAINT
SQL> ALTER TABLE t ADD CONSTRAINT t_chk CHECK(
2 (starting_date > prenotation_date + 3)
3 AND (starting_date < ending_date)
4 AND (TRUNC(starting_date) = TRUNC(ending_date))
5 );
Table altered.
SQL>
INSERT : check starting_date > prenotation_date + 3. Below insert should fail.
SQL> INSERT
2 INTO t VALUES
3 (
4 to_date('03/01/2015 00:00:00','mm/dd/yyyy hh24:mi:ss'),
5 to_date('03/02/2015 08:00:00','mm/dd/yyyy hh24:mi:ss'),
6 to_date('03/02/2015 10:00:00','mm/dd/yyyy hh24:mi:ss')
7 );
INSERT
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated
SQL>
INSERT : check starting_date < ending_date. Below insert should fail.
SQL> INSERT
2 INTO t VALUES
3 (
4 to_date('02/01/2015 00:00:00','mm/dd/yyyy hh24:mi:ss'),
5 to_date('03/02/2015 10:00:00','mm/dd/yyyy hh24:mi:ss'),
6 to_date('03/02/2015 08:00:00','mm/dd/yyyy hh24:mi:ss')
7 );
INSERT
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated
SQL>
INSERT : check booking is done for the same day. Below insert should fail. Note the time 08:00:00 and 10:00:00 for start and end time respectively.
SQL> INSERT
2 INTO t VALUES
3 (
4 to_date('02/01/2015 00:00:00','mm/dd/yyyy hh24:mi:ss'),
5 to_date('03/02/2015 08:00:00','mm/dd/yyyy hh24:mi:ss'),
6 to_date('04/02/2015 10:00:00','mm/dd/yyyy hh24:mi:ss')
7 );
INSERT
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated
SQL>
INSERT : All values satisfy the requirement, below insert should pass.
SQL> INSERT
2 INTO t VALUES
3 (
4 to_date('02/01/2015 00:00:00','mm/dd/yyyy hh24:mi:ss'),
5 to_date('03/02/2015 08:00:00','mm/dd/yyyy hh24:mi:ss'),
6 to_date('03/02/2015 10:00:00','mm/dd/yyyy hh24:mi:ss')
7 );
1 row created.
SQL>