How could I default a date column as 21 days ahead of another date column in oracle?

Question

Here is my table (with the working columns including primary key removed for simplicity)

 CREATE TABLE loans
    (checkout_date DATE,
     due_date DATE DEFAULT 'checkout_date' + 21,
     )

This code returns "ORA-00932: inconsistent datatypes: expected DATE got NUMBER". With '21' instead returns the same error. Any help is appreciated!

Answer 1

I don't think that Oracle supports setting a default from another column. One workaround would be to use a trigger, but that might be overkill here. Why not just put the logic in a view?

create view v_loans as 
select checkout_date, coalesce(due_date, checkout_date + 21) as due_date from loans

Or we can get a little fancy with a computed column:

create table loans (
    checkout_date date,
    due_date      date,
    real_due_date date as (coalesce(due_date, checkout_date + 21))
);

Then you would use column real_due_date instead of due_date in your queries.

For the whole thing to make more sense, you would probably need a not null constraint on column checkout_date.

Side note: as commented by mathghy and Thorsten Kettner, this does not strictly implements the concept of DEFAULT, because, even if a NULL value is explictely assign to the column, the view (and the computed column) would still apply the replacement logic (which a DEFAULT would not do).