I want to use (between, and, or) conditions in a check constraint at the same time. For example:

Alter table called_no add constraint called_no_chck
   check ("call_time" between '01:00:00 AM' and '12:59:59 AM' or
          between '01:00:00 PM' and '12:59:59 PM')

CodePudding user response：

You certainly may use multiple conditions in a single check constraint.

Your attempt fails because you wrote an invalid condition. The same condition would fail if it was in the where clause of a query, or in any other place where you need a condition.

The correct way to check that a date-time is between A and B or between C and D looks like this:

...check (call_time between A and B OR call_time between C and D)
                                       ^^^^^^^^^

You understand the shorthand, where you don't repeat the column name (call_time) before the second between. I understand it too. A computer does not - they are not human. OR must appear between two conditions. A condition such as between must be applied to something, and you can't leave that "something" out and expect the computer to understand it's the same "something" you already used in another condition.

There are other mistakes in your condition though. 12:59:59 AM is about one hour after midnight; it's not about one hour after noon, as you seem to believe. Nothing is ever between 1 AM and 12:59:59 AM (of the same day), because 1 AM is after 12:59:59 AM.

It's also not clear what the data type of your column is. If it's date (as it almost surely should be), you can't just compare it to strings (as you have in your condition). And, you can't simply compare a date-time to something that's just time-of-day.

All these mistakes, though, are out of the scope of your question as you asked it (which was: Can you use "compound" conditions in a constraint; the answer, again, is YES).

CodePudding user response：

Do not use a string column.

If you just want a time without a date, use an INTERVAL DAY(0) TO SECOND(0) column.

If you want a date-time column with accuracy down to whole seconds (and no time-zone) then use a DATE column (which always has the components year, month, day, hour, minute and seconds).

With either of those two options, you do not need a CHECK constraint as it will not accept invalid times.

If you want to format the value using the 12-hour clock then you can use a virtual column.

CREATE TABLE table_name (
  time               INTERVAL DAY(0) TO SECOND(0),
  datetime           DATE,
  formatted_time     VARCHAR2(11)
                     GENERATED ALWAYS AS (TO_CHAR(DATE '1970-01-01'   time, 'HH12:MI:SS AM')),
  formatted_datetime VARCHAR2(11)
                     GENERATED ALWAYS AS (TO_CHAR(datetime, 'HH12:MI:SS AM'))
);

INSERT INTO table_name (time, datetime)
SELECT INTERVAL '00:12:34' HOUR TO SECOND, SYSDATE FROM DUAL UNION ALL
SELECT INTERVAL '12:34:56' HOUR TO SECOND, SYSDATE   0.5 FROM DUAL;

Then:

SELECT * FROM table_name;

Outputs:

TIME	DATETIME	FORMATTED_TIME	FORMATTED_DATETIME
0 00:12:34	2021-12-18 15:37:34	12:12:34 AM	03:35:47 PM
0 12:34:56	2021-12-19 03:37:34	12:34:56 PM	03:35:47 AM

If you are storing it in a string (don't) then, yes, you can use multiple conditions in a single constraint:

CREATE TABLE table_name (
  time VARCHAR2(11)
       CHECK (
             SUBSTR(time,  1, 2) BETWEEN '01' AND '12'
         AND SUBSTR(time,  4, 2) BETWEEN '00' AND '59'
         AND SUBSTR(time,  7, 2) BETWEEN '00' AND '59'
         AND SUBSTR(time, 10, 2) IN ('AM', 'PM')
         AND time LIKE '__:__:__ __'
       )
);

or multiple constraints:

CREATE TABLE table_name (
  time VARCHAR2(11)
       CONSTRAINT invalid_hours    CHECK (SUBSTR(time,  1, 2) BETWEEN '01' AND '12')
       CONSTRAINT invalid_minutes  CHECK (SUBSTR(time,  4, 2) BETWEEN '00' AND '59')
       CONSTRAINT invalid_seconds  CHECK (SUBSTR(time,  7, 2) BETWEEN '00' AND '59')
       CONSTRAINT invalid_meridian CHECK (SUBSTR(time, 10, 2) IN ('AM', 'PM'))
       CONSTRAINT invalid_format   CHECK (time LIKE '__:__:__ __')
);

But its much easier to not implement all those constraints and just use either an INTERVAL or a DATE when you won't be allowed to enter invalid data.

db<>fiddle here