无法添加符合条件的SQL虚拟列：如何计算每行总座位数

现有表结构与序列 #

以下是row（行）表、seat（座位）表的创建语句，以及对应的序列定义：

row表与序列 #

CREATE TABLE "row"(
"rowID" NUMBER(5) PRIMARY KEY, -- 行ID，主键
numbers NUMBER(5) NOT NULL, -- 行号
seats NUMBER(5) NOT NULL, -- 该行设计的总座位数
auditoriumID NUMBER(5), -- 所属礼堂ID
CONSTRAINT row_aud_fk FOREIGN KEY(auditoriumID) REFERENCES auditorium(auditoriumID)); -- 关联礼堂表的外键

CREATE SEQUENCE seq_row
START WITH 10 -- 起始值10
INCREMENT BY 1 -- 步长1
MAXVALUE 100 -- 最大值100
NOCYCLE -- 达到最大值后停止增长
CACHE 5; -- 缓存5个序列值

seat表与序列 #

CREATE TABLE seat(
seatID NUMBER(5) PRIMARY KEY, -- 座位ID，主键
"rowID" NUMBER(5), -- 所属行ID
numbers NUMBER(5) NOT NULL, -- 座位序号
"name" NVARCHAR2(26) NOT NULL, -- 座位名称
typeID NUMBER(5), -- 座位类型ID
CONSTRAINT seat_row_fk FOREIGN KEY("rowID") REFERENCES "row"("rowID"), -- 关联行表的外键
CONSTRAINT seatType_fk FOREIGN KEY(typeID) REFERENCES "seatType"("typeID")); -- 关联座位类型表的外键

CREATE SEQUENCE seq_singularseat
START WITH 1000 -- 起始值1000
INCREMENT BY 1 -- 步长1
MAXVALUE 10000 -- 最大值10000
NOCYCLE -- 达到最大值后停止增长
CACHE 5; -- 缓存5个序列值

问题说明 #

尝试用聚合子查询给seat表添加计算每行总座位数的虚拟列时执行失败，失败代码如下：

ALTER TABLE seat
ADD total_seats_per_row NUMBER GENERATED ALWAYS AS
  (SELECT COUNT(*) FROM seat s WHERE s.rowID = seat.rowID) VIRTUAL;

要求必须使用算术表达式实现该需求，可选择在seat或row表中添加虚拟列。

解决方案 #

方案1：在row表添加虚拟列（推荐） #

row表本身已存储seats字段，记录了该行设计的总座位数，直接通过算术表达式引用该字段即可生成虚拟列：

ALTER TABLE "row"
ADD total_seats_per_row NUMBER GENERATED ALWAYS AS (seats) VIRTUAL;

注：单一字段引用属于Oracle允许的算术表达式范畴，且该方式无需关联其他表，性能最优。

方案2：在seat表添加虚拟列 #

若必须在seat表中添加，可通过单行子查询引用row表的seats字段（该子查询返回单一值，符合算术表达式要求）：

ALTER TABLE seat
ADD total_seats_per_row NUMBER GENERATED ALWAYS AS (
  (SELECT seats FROM "row" r WHERE r."rowID" = seat."rowID")
) VIRTUAL;

补充说明 #

Oracle虚拟列的表达式要求：必须是确定性的、能返回单一值的表达式，不允许使用聚合函数或返回多行数据的子查询。因此直接复用row表中已有的seats字段是最合规的实现方式。

内容的提问来源于stack exchange，提问作者Reyan Tariq