时间戳

首页 > 解决方案 > SQL 错误代码 ORA-00904: : 无效标识符 00904. 00000 - 创建带约束的表时出现“%s: 无效标识符”

sql - SQL 错误代码 ORA-00904: : 无效标识符 00904. 00000 - 创建带约束的表时出现“%s: 无效标识符”

问题描述

创建下表时出现错误代码

ORA-00904: 无效标识符 00904. 00000 - "%s: 无效标识符"

CREATE TABLE Staff (
    staffNo     VARCHAR2(5),
    fName       VARCHAR2(10),
    lName       VARCHAR2(10),
    position    VARCHAR2(10),
    DOB         DATE,
    salary      NUMBER(7,2)        NOT NULL,
    branchNo    CHAR(4),
    supervisor  VARCHAR2(5),
    CONSTRAINT Staff_PK     PRIMARY KEY (staffNo),
    CONSTRAINT Staff_AK     UNIQUE      (fName, lName, branchNo),
    CONSTRAINT Staff_FK     FOREIGN KEY     (branchNo) REFERENCES Branch (branchNo),
    CONSTRAINT Staff_pos    CHECK       (position IN ('Manager', 'Supervisor')),
    CONSTRAINT Staff_fName  CHECK       (NOT (fname IS NULL)),
    CONSTRAINT Staff_lName  CHECK       (lName IS NOT NULL),
    CONSTRAINT Staff_branch CHECK       (branchNo IS NOT NULL),
    CONSTRAINT Staff_position <> 'manager' OR 'supervisor' IS NULL,
    CONSTRAINT 'A staff member can supervise up to 10 others.',
    CONSTRAINT 'A staff member who supervises others is in the position of supervisor or 
        manager'
);

我认为这个问题可能与限制有关，但我不确定。这是我第一次编码和使用 Oracle。任何帮助/指导将不胜感激。

标签： sqloracleconstraintscreate-tableora-00904

解决方案

您是否在尝试创建表 STAFF 之前创建表 BRANCH？如果是，那么这应该有效。CONSTRAINT Staff_FK FOREIGN KEY (branchNo) REFERENCES Branch (branchNo)如果否，则首先从 STAFF 表定义中删除创建表 BRANCH，然后将创建 STAFF 表。您可以在创建 BRANCH TABLE 后添加CONSTRAINT Staff_FK FOREIGN KEY (branchNo) REFERENCES Branch (branchNo)using ALTER TABLE STAFF 语句。

“REFERENCES Branch (branchNo)”需要表分支来保证引用完整性，最好定义一个NOT NULL列而不是使用CHECK来避免NULLS。

CREATE TABLE Staff (
    staffNo     VARCHAR2(5),
    fName       VARCHAR2(10) NOT NULL ,
    lName       VARCHAR2(10) NOT NULL ,
    position    VARCHAR2(10) NOT NULL ,
    DOB         DATE,
    salary      NUMBER(7,2)        NOT NULL,
    branchNo    CHAR(4) NOT NULL ,
    supervisor  VARCHAR2(5),
    CONSTRAINT Staff_PK     PRIMARY KEY (staffNo),
    CONSTRAINT Staff_AK     UNIQUE      (fName, lName, branchNo),
    CONSTRAINT Staff_FK     FOREIGN KEY     (branchNo) REFERENCES Branch (branchNo),
    CONSTRAINT Staff_pos  CHECK( position IN ('Manager', 'Supervisor')),
    CONSTRAINT Staff_position CHECK (position <> 'manager' OR supervisor IS NULL)

    /* CONSTRAINT 'A staff member can supervise up to 10 others.',
    CONSTRAINT 'A staff member who supervises others is in the position of supervisor or manager' -- you need a trigger for these)*/
);

为此，“一名工作人员最多可以监督其他 10 人。” 您不能直接在表定义中创建某些内容。您可以尝试使用触发器将主管下的员工人数限制为 10 人。

推荐阅读