关于Oracle数据库的问题

Question

问题遇到的现象和发生背景

在建立的数据表student中设置一个字段CreatedBy的默认值为另外一个字段sno

问题相关代码，请勿粘贴截图

运行结果及报错内容

我的解答思路和尝试过的方法

我想要达到的结果

Answer 1

参考oracle官方文档 ,

CREATE TABLE https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

The DEFAULT expression can include any SQL function as long as the function does not return a literal argument, a column reference, or a nested function invocation
The DEFAULT expression can include the sequence pseudocolumns CURRVAL and NEXTVAL, as long as the sequence exists and you have the privileges necessary to access it
A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified
The expression can be of any form except a scalar subquery expression

简单来说,在create table的语法中,字段的默认值可以为很多东西,但是不能为另一个列(序列伪列除外)。
因此,建议采用触发器的形式

--测试表
create table test_20220327 (sno varchar2(10),create_by varchar2(10) );
--触发器
create or replace trigger TEST_20220327_create_by_tri
  before insert on TEST_20220327
  for each row
declare
begin
  :new.create_by := :new.sno;
end;
/
--插入数据
insert into test_20220327(sno) values ('abc');
--查询结果
select * from test_20220327;