Set ORACLE table fields default value to a formula

Question

I have an oracle table like this:

create table tms_transaction_tbl
(
trans_id number primary key,
location_id number,
trans_date date,
resource_id number,
ts_id number,
max_value number,
booked_units number default 0,
remaining number default (select max_value-booked_units),
booked number not null ,
user_id number,
trans_time timestamp
);

as you can see I tried to set default value of remaining to (max_value-booked_units)

remainging number default (select max_value-booked_units),

but it gives me this error this error

ora-22818:subquery expression not allowed here

Answer 1

You cannot refer to other columns in the DEFAULT expression

Here is the snippet from Oracle Documentation

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

Answer 2

You can't use a SELECT as a default value, it must be a constant.

If you're using Oracle 11g this is what virtual columns are for. You can't insert into or update them but the provide a pre-calculated column in the database for you.

create table tms_transaction_tbl
 ( trans_id number primary key,
   location_id number,
   trans_date date,
   resource_id number,
   ts_id number,
   max_value number,
   booked_units number default 0,
   remaining number generated always as ( max_value - booked_units ) virtual,
   booked number not null ,
   user_id number,
   trans_time timestamp
   );

The syntax is further described in the documentation for the CREATE TABLE statement. If you're not using 11g you can achieve the same logic with a view on top of your table.

If you don't want to use a view or a virtual column then I can only recommend that you do not store this data at all.