create table holidays(
          holiday_date DATE not null,
          holiday_name VARCHAR2(20),
          constraint holidays_pk primary key (holiday_date),
          constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
        );

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
        WITH dts as (
          select to_date('25-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Thanksgiving 2021' from dual union all
          select to_date('29-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Hanukkah 2021' from dual
        )
        SELECT * from dts;

SELECT constraint_name, constraint_type, column_name
from user_constraints  natural join user_cons_columns
where table_name = 'HOLIDAYS';

CONSTRAINT_NAME    CONSTRAINT_TYPE    COLUMN_NAME
SYS_C0075523509    C    HOLIDAY_DATE
IS_MIDNIGHT    C    HOLIDAY_DATE
HOLIDAYS_PK    P    HOLIDAY_DATE