sql developer中出现错误ora-00904在创建表时,似乎找不到任何在线修复程序

7lrncoxx  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(253)

我已经看到许多关于看起来臭名昭著的ora-00904的帖子,但似乎没有任何帮助。我正在创建两个表(现在,后面还有更多),如下所示;

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
);

CREATE TABLE Customer(
    CustomerID INT NOT NULL PRIMARY KEY,
    ProjectID INT NOT NULL,
    FOREIGN KEY(ProjectID) REFERENCES Project(ProjectID), /*This is yet another table with a foreign key which needs to be sorted*/
    CustomerName Char(255) NOT NULL,
    PhoneNumber INT NOT NULL,
    Region CHAR(255) NOT NULL
);

但每次我运行它,我都会出错;

Error starting at line : 4 in command -
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY(customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)
Error report -
ORA-00904: "CUSTOMERID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

* Cause:
* Action:

任何帮助都将不胜感激!

9w11ddsr

9w11ddsr1#

默认情况下,如果不加引号,oracle会将所有名称转换为大写(编辑:在回答这个问题时,起始文章中的外键约束确实包含一个带引号的列名)。
因此,您的约束尝试查找名为“customerid”的列,而您的列的真实名称是“customerid”。
或者引用你的专栏:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    "customerID" INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY ("customerID") REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

不要在外键约束上使用引号:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

或者在外键约束中使用引号并大写:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY ("CUSTOMERID") REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

如前所述,建议保持样式一致以防止将来出现问题,因此选项2将是最简单的解决方案。否则,您需要引用所有列以保持一致。
/编辑:我刚试过-这绝对适用于oracle 12c:

CREATE TABLE Customer (
CustomerID INT,
PRIMARY KEY (CustomerID)
)

然后:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY(customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)
oalqel3c

oalqel3c2#

引用的表需要在外键引用之前创建。一种方法是先创建表,然后添加外键引用:

CREATE TABLE Employee (
    EmployeeID int NOT NULL,
    PRIMARY KEY (EmployeeID),
    customerID INT NOT NULL,
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
);

CREATE TABLE Customer (
    CustomerID INT NOT NULL PRIMARY KEY,
    ProjectID INT NOT NULL,
    -- FOREIGN KEY(ProjectID) REFERENCES Project(ProjectID), /*This is yet another table with a foreign key which needs to be sorted*/
    CustomerName Char(255) NOT NULL,
    PhoneNumber INT NOT NULL,
    Region CHAR(255) NOT NULL
);

alter table Employee add CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES Customer (CustomerID)

这是一把小提琴。
请注意,我将外键约束注解为 Project .
另外,在大多数数据模型中,您只需对表创建进行排序,外键“链接”中没有循环。但是,因为您尚未定义 Project ,我建议采用更一般的方法。

相关问题