Oracle creating composite primary key with constants

Question

Using oracle, how can I create a composite PRIMARY key with a constraint check.

Below is a sample of what would like to accomplish

create table T ( 
  n int,
dt DATE,
constraint t_pk primary key (n,
(dt=trunc(dt, 'dd')));

user_266304 · Answer

If you only want to allow dates with times set to midnight then use a separate check constraint:

create table T ( 
  n int,
  dt DATE,
  constraint t_chk check (dt=trunc(dt, 'dd')),
  constraint t_pk primary key (n, dt)
);

If you want to allow non-midnight times but only one per day then you can use a virtual column, as @gsalem also suggested:

create table T ( 
  n int,
  dt DATE,
  dd DATE generated always as (trunc(dt)),
  constraint t_pk primary key (n, dd)
 );

db<>fiddle showing both; notice the different times in the output of the two select statements.

user_7398653 · Answer

Not withstanding the correctness of your stated requirement, you can do it by using virtual columns :

    create table T ( 
  n int,
dt DATE check (dt=trunc(dt,'dd')),
dt1 generated always as (trunc(dt,'dd')),
constraint t_pk primary key (n,
dt1));