Oracle创建对象关系表DEPARTMENT报ORA-02330错误如何解决

执行DEPARTMENT对象表创建语句抛出ORA-02330错误，是外键约束的语法写法不规范导致的：

• Oracle数据库中，列级内联约束定义外键时，不需要写FOREIGN KEY关键字，FOREIGN KEY仅在表级约束定义场景下使用，作用是显式指定外键关联的列。
• 原语句CREATE TABLE DEPARTMENT OF DEPT_TYPE (EMP_ID FOREIGN KEY REFERENCES EMPLOYEE(EMP_ID))混用了内联约束和表级约束的语法，SQL解析器会将位置错误的FOREIGN KEY识别为非法的字段类型声明，直接触发类型不允许的报错。
• 两个对象类型中定义的EMP_ID字段均为INTEGER类型，不存在字段类型不匹配问题，不需要修改已创建的EMP_TYPE、EMPLOYEE表结构。

以下两种写法均符合Oracle对象表的创建语法，可任选一种执行：

写法1：列级内联约束（改动最小） #

直接删掉内联约束里多余的FOREIGN KEY关键字即可，建议同时给DEPARTMENT表设置主键，符合常规表设计规范：

CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT(
DEPT_ID INTEGER,
EMP_ID INTEGER,
DEPT_NAME VARCHAR2(20),
LOCATION VARCHAR2(20));
/

CREATE TABLE DEPARTMENT OF DEPT_TYPE (
  DEPT_ID PRIMARY KEY,
  EMP_ID REFERENCES EMPLOYEE(EMP_ID)
);
/

写法2：表级约束定义（更易维护，可自定义约束名） #

如果需要自定义外键约束名、后续方便维护约束，可以用表级约束的写法，显式声明FOREIGN KEY：

CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT(
DEPT_ID INTEGER,
EMP_ID INTEGER,
DEPT_NAME VARCHAR2(20),
LOCATION VARCHAR2(20));
/

CREATE TABLE DEPARTMENT OF DEPT_TYPE (
  DEPT_ID PRIMARY KEY,
  CONSTRAINT fk_dept_emp FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID)
);
/

表创建完成后，可以执行以下SQL查询约束状态，确认外键生效：

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('EMPLOYEE', 'DEPARTMENT');

查询结果中DEPARTMENT表下存在约束类型为R（即外键约束）、指向EMPLOYEE表的记录，即说明外键创建成功。

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