此处不允许使用 Oracle 列默认值

Question

这个问题已经有答案了:

Autoincrement in oracle to already created table (1 个回答)

已关闭 7 年前。

问题出在第 4 行使用序列的 DEFAULT 值。

    CREATE OR REPLACE SEQUENCE CHANNEL_SEQ START WITH 1 INCREMENT BY 1;

    CREATE TABLE "CHANNEL" (  
    "ID_CHANNEL" NUMBER(18,0) DEFAULT CHANNEL_SEQ.NEXTVAL,  
    "IS_ACTIVE" VARCHAR2(1 CHAR) NOT NULL,  
    "BATCH_SIZE" NUMBER(3,0) NOT NULL,
    "MAX_DOCS_IN_PROCESS" NUMBER(4,0) NOT NULL,
    "RECEIVER_ID" NUMBER(18,0) NOT NULL,
    "LAST_POS_SESSION_TIME" DATE,
     CONSTRAINT "PK_CHANNEL" PRIMARY KEY ("ID_CHANNEL"),
     CONSTRAINT "FK_RECEIVER_ID_CHANNEL" FOREIGN KEY ("RECEIVER_ID") REFERENCES   "MSG_OUT"("MSG_OUT_ID"),
     CONSTRAINT "CHK_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N'))
   );

错误消息是:

"SQL Error: ORA-00984: column not allowed here"

欢迎所有帮助和提示。

Answer 1

您不能使用seq.NEXTVAL作为默认值。

为此目的(自动填充)，您应该创建触发器，例如

CREATE OT REPLACE TRIGGER 
BEFORE INSERT 
ON CHANNEL
FOR EACH ROW
BEGIN
:NEW.ID_CHANNEL:=CHANNEL_SEQ.NEXTVAL;
END;