Oracle中ORA-00900无效SQL语句错误排查及关联表脚本求助

核心错误原因 #

1. 非法注释语法：Oracle SQL仅支持--开头的单行注释或/* ... */的多行注释，你使用的<--Table ...-->是HTML注释格式，Oracle无法识别，这是触发ORA-00900的直接原因。
2. 外键定义不规范：
   • OPTION TWO中的外键未指定REFERENCES子句，Oracle要求外键必须明确关联到父表的主键/唯一键。
   • RESPONSIBLE_BY_TYPE表的所有外键使用了相同的约束名FK_RESPONSIBLE_BY_TYPE，约束名必须唯一。
   • 引用RESPONSIBLE_TYPE时列名错误：该表的描述列是RESPONSIBLE_TYPE_DESC，你写的RESPONSIBLE_TYPE列不存在。
   • RESPONSIBLE_BY_TYPE的最后一个外键定义缺少闭合的)。
3. 表设计逻辑偏差：IDENT表的复合主键设计（OPTION ONE）不符合关联逻辑，三类支出ID应各自关联到RESPONSIBLE表，而非作为复合主键。

修正后的完整SQL脚本 #

-- Table RESPONSIBLE
INSERT INTO RESPONSIBLE(RESPONSIBLE_ID, RESPONSIBLE_DESC) VALUES (1, 'payer1');
INSERT INTO RESPONSIBLE(RESPONSIBLE_ID, RESPONSIBLE_DESC) VALUES (2, 'payer2');

ALTER TABLE RESPONSIBLE ADD CONSTRAINT PK_RESPONSIBLE_ID PRIMARY KEY (RESPONSIBLE_ID);
ALTER TABLE RESPONSIBLE ADD CONSTRAINT UNIQUE_RESPONSIBLE_DESC UNIQUE (RESPONSIBLE_DESC);


-- Table IDENT
-- 修正：外键关联到RESPONSIBLE表的主键
ALTER TABLE IDENT ADD CONSTRAINT FK_RESPONSIBLE_DEBTS_ID FOREIGN KEY (RESPONSIBLE_DEBTS_ID) REFERENCES RESPONSIBLE(RESPONSIBLE_ID);
ALTER TABLE IDENT ADD CONSTRAINT FK_RESPONSIBLE_ASSETS_ID FOREIGN KEY (RESPONSIBLE_ASSETS_ID) REFERENCES RESPONSIBLE(RESPONSIBLE_ID);
ALTER TABLE IDENT ADD CONSTRAINT FK_RESPONSIBLE_EXPENSES_ID FOREIGN KEY (RESPONSIBLE_EXPENSES_ID) REFERENCES RESPONSIBLE(RESPONSIBLE_ID);


-- Table RESPONSIBLE_TYPE
CREATE TABLE RESPONSIBLE_TYPE(RESPONSIBLE_TYPE_ID NUMBER(10), RESPONSIBLE_TYPE_DESC VARCHAR(100));

INSERT INTO RESPONSIBLE_TYPE(RESPONSIBLE_TYPE_ID, RESPONSIBLE_TYPE_DESC) VALUES (1, 'Assets');
INSERT INTO RESPONSIBLE_TYPE(RESPONSIBLE_TYPE_ID, RESPONSIBLE_TYPE_DESC) VALUES (2, 'Expenses');
INSERT INTO RESPONSIBLE_TYPE(RESPONSIBLE_TYPE_ID, RESPONSIBLE_TYPE_DESC) VALUES (3, 'Debts');

ALTER TABLE RESPONSIBLE_TYPE ADD CONSTRAINT PK_RESPONSIBLE_TYPE_ID PRIMARY KEY (RESPONSIBLE_TYPE_ID);
ALTER TABLE RESPONSIBLE_TYPE ADD CONSTRAINT UNIQUE_RESPONSIBLE_TYPE_DESC UNIQUE (RESPONSIBLE_TYPE_DESC);


-- Table RESPONSIBLE_BY_TYPE
-- 修正：约束名唯一、列名正确、补全闭合括号
CREATE TABLE RESPONSIBLE_BY_TYPE(
    RESPONSIBLE_ID NUMBER(10) NOT NULL, 
    RESPONSIBLE_EXPENSES_ID NUMBER(10) NOT NULL,
    RESPONSIBLE_ASSETS_ID NUMBER(10) NOT NULL, 
    RESPONSIBLE_DEBTS_ID NUMBER(10) NOT NULL, 
    RESPONSIBLE_DESC VARCHAR(100), 
    RESPONSIBLE_TYPE_DESC VARCHAR(100), 
    CONSTRAINT FK_RBT_RESPONSIBLE_ID FOREIGN KEY(RESPONSIBLE_ID) REFERENCES RESPONSIBLE(RESPONSIBLE_ID),
    CONSTRAINT FK_RBT_EXPENSES_ID FOREIGN KEY(RESPONSIBLE_EXPENSES_ID) REFERENCES IDENT(RESPONSIBLE_EXPENSES_ID),
    CONSTRAINT FK_RBT_ASSETS_ID FOREIGN KEY(RESPONSIBLE_ASSETS_ID) REFERENCES IDENT(RESPONSIBLE_ASSETS_ID),
    CONSTRAINT FK_RBT_DEBTS_ID FOREIGN KEY(RESPONSIBLE_DEBTS_ID) REFERENCES IDENT(RESPONSIBLE_DEBTS_ID),
    CONSTRAINT FK_RBT_RESPONSIBLE_TYPE FOREIGN KEY(RESPONSIBLE_TYPE_DESC) REFERENCES RESPONSIBLE_TYPE(RESPONSIBLE_TYPE_DESC),
    CONSTRAINT FK_RBT_RESPONSIBLE_DESC FOREIGN KEY(RESPONSIBLE_DESC) REFERENCES RESPONSIBLE(RESPONSIBLE_DESC)
);

额外说明 #

• 若IDENT表已有数据，添加外键前需确保所有RESPONSIBLE_DEBTS_ID、RESPONSIBLE_ASSETS_ID、RESPONSIBLE_EXPENSES_ID的值都存在于RESPONSIBLE表的RESPONSIBLE_ID中，否则会触发约束违反错误。
• RESPONSIBLE_BY_TYPE表可进一步优化：无需冗余存储RESPONSIBLE_DESC和RESPONSIBLE_TYPE_DESC，通过关联主键即可查询对应描述，减少数据冗余。

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