oracle - 将 ORACLE 表字段默认值设置为公式

Question

我有一个像这样的 oracle 表:

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
);

如您所见，我尝试将剩余的默认值设置为 (max_value-booked_units)

remainging number default (select max_value-booked_units),

但它给了我这个错误这个错误

ora-22818:subquery expression not allowed here

Answer 1

您不能在 DEFAULT 表达式中引用其他列

这是来自 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.