创建员工表时触发ORA-00902错误：无效数据类型问题求助

错误原因 #

• Oracle数据库不支持MONEY数据类型，你在DOC和STAFF表的SALARY字段中使用了该类型，这是触发ORA-00902错误的核心原因。
• 额外提示：Oracle中NUMERIC类型虽然可用，但官方更推荐使用NUMBER（二者功能完全等价）；FLOAT类型精度不可控，建议用NUMBER(p,s)替代以保证数值精度。

解决方法 #

1. 替换无效数据类型：把所有MONEY替换为Oracle支持的NUMBER类型，比如NUMBER(10,2)（适合存储薪资类数值，保留两位小数）。
2. 可选优化：将NUMERIC替换为NUMBER，FLOAT替换为指定精度的NUMBER（例如BALANCE NUMBER(12,2)）。
3. 简化约束语句：Oracle中ON UPDATE NO ACTION是默认约束行为，无需显式声明（Oracle默认不允许更新主键值）。

修正后的完整SQL #

CREATE TABLE BUILDING 
(
    BLDG_ID NUMBER NOT NULL , 
    BLDG_DESC VARCHAR(100) NOT NULL 
);

ALTER TABLE BUILDING 
    ADD CONSTRAINT BUILDING_PK PRIMARY KEY (BLDG_ID);

CREATE TABLE DEPARTMENT 
(
    DEPT_ID NUMBER NOT NULL , 
    DEPT_DESC VARCHAR(100) NOT NULL 
);

ALTER TABLE DEPARTMENT 
    ADD CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DEPT_ID);

CREATE TABLE DOC 
(
    DOC_ID NUMBER NOT NULL , 
    DOC_LNAME VARCHAR(50) NOT NULL , 
    DOC_FNAME VARCHAR(50) NOT NULL , 
    BIRTHDAY DATE NOT NULL , 
    DEPT_ID NUMBER NOT NULL , 
    DPOSITION VARCHAR(50) NOT NULL , 
    SALARY NUMBER(10,2) 
);

ALTER TABLE DOC 
    ADD CONSTRAINT DOC_PK PRIMARY KEY (DOC_ID);

CREATE TABLE PATIENTS 
(
    PATIENT_ID NUMBER NOT NULL , 
    PATIENT_LNAME VARCHAR(50) , 
    PATIENT_FNAME VARCHAR(50) , 
    BIRTHDAY DATE , 
    DOC_ID NUMBER NOT NULL , 
    ROOM_ID NUMBER NOT NULL , 
    BALANCE NUMBER(12,2) 
);

ALTER TABLE PATIENTS 
    ADD CONSTRAINT PATIENTS_PK PRIMARY KEY (PATIENT_ID);

CREATE TABLE ROOM 
(
    ROOM_ID NUMBER NOT NULL , 
    ROOM_DESC VARCHAR(50) NOT NULL , 
    BLDG_ID NUMBER NOT NULL 
);

ALTER TABLE ROOM 
    ADD CONSTRAINT ROOM_PK PRIMARY KEY (ROOM_ID);

CREATE TABLE STAFF 
(
    STAFF_ID NUMBER NOT NULL , 
    STAFF_LNAME VARCHAR(50) NOT NULL , 
    STAFF_FNAME VARCHAR(50) NOT NULL , 
    BIRTHDAY DATE NOT NULL , 
    DEPT_ID NUMBER NOT NULL , 
    SPOSITION VARCHAR(50) NOT NULL , 
    SALARY NUMBER(10,2) NOT NULL 
);

ALTER TABLE STAFF 
    ADD CONSTRAINT STAFF_PK PRIMARY KEY (STAFF_ID);

ALTER TABLE DOC 
    ADD CONSTRAINT DEPT_FK 
        FOREIGN KEY (DEPT_ID) 
        REFERENCES DEPARTMENT (DEPT_ID) 
            ON DELETE NO ACTION;

ALTER TABLE PATIENTS 
    ADD CONSTRAINT PATIENTS_DOC_FK 
        FOREIGN KEY (DOC_ID) 
        REFERENCES DOC (DOC_ID) 
            ON DELETE NO ACTION;

ALTER TABLE PATIENTS 
    ADD CONSTRAINT PATIENTS_ROOM_FK 
        FOREIGN KEY (ROOM_ID) 
        REFERENCES ROOM (ROOM_ID) 
            ON DELETE NO ACTION;

ALTER TABLE ROOM 
    ADD CONSTRAINT ROOM_BUILDING_FK 
        FOREIGN KEY (BLDG_ID) 
        REFERENCES BUILDING (BLDG_ID) 
            ON DELETE NO ACTION;

ALTER TABLE STAFF 
    ADD CONSTRAINT STAFF_DEPARTMENT_FK 
        FOREIGN KEY (DEPT_ID) 
        REFERENCES DEPARTMENT (DEPT_ID) 
            ON DELETE NO ACTION;

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