创建EMPLOYEE表时出现外键缺括号及右括号缺失错误求助

Let's break down the issues in your SQL code and fix them step by step:

1. Incorrect Foreign Key Syntax #

You tried to declare foreign keys inline with column definitions but used invalid syntax. For inline foreign key declarations, you don't need the FOREIGN KEY keyword—just append REFERENCES [table](column) directly to the column definition. If you want to explicitly name your foreign key constraints (a good practice for easier debugging later), you'll need to define them separately after all column declarations.

2. Trailing Comma Before Closing Parenthesis #

Your SALARY column line ends with an extra comma, which triggers the "missing right parenthesis" error. Always double-check for stray trailing commas in your column lists!

3. Table Creation Order #

Since EMPLOYEE references the DEPARTMENT table, you must create DEPARTMENT first. Otherwise, the database won't recognize the parent table when setting up the foreign key.

Corrected SQL Code #

-- Create DEPARTMENT first (parent table must exist before child table)
CREATE TABLE DEPARTMENT (
    DNO INT NOT NULL PRIMARY KEY,
    DNAME VARCHAR(50),
    LOCATION VARCHAR(50) DEFAULT('NEW DELHI')
);

-- Fixed EMPLOYEE table with valid syntax
CREATE TABLE EMPLOYEE (
    EID CHAR(3) NOT NULL PRIMARY KEY,
    ENAME VARCHAR2(50) NOT NULL,
    JOB_TYPE VARCHAR2(50) NOT NULL,
    MANAGER CHAR(3) REFERENCES EMPLOYEE(EID), -- Valid inline foreign key
    HIRE_DATE DATE NOT NULL,
    DNO INTEGER REFERENCES DEPARTMENT(DNO), -- Valid inline foreign key
    COMMISSION DECIMAL(10,2),
    SALARY DECIMAL(7,2) NOT NULL -- Removed trailing comma
);

Optional: Explicitly Named Foreign Keys #

If you want more control over constraint names (useful for error messages and schema management), define foreign keys separately:

CREATE TABLE EMPLOYEE (
    EID CHAR(3) NOT NULL PRIMARY KEY,
    ENAME VARCHAR2(50) NOT NULL,
    JOB_TYPE VARCHAR2(50) NOT NULL,
    MANAGER CHAR(3),
    HIRE_DATE DATE NOT NULL,
    DNO INTEGER,
    COMMISSION DECIMAL(10,2),
    SALARY DECIMAL(7,2) NOT NULL,
    -- Named foreign key constraints
    CONSTRAINT fk_employee_manager FOREIGN KEY (MANAGER) REFERENCES EMPLOYEE(EID),
    CONSTRAINT fk_employee_dept FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNO)
);

This should resolve both the "foreign key missing parenthesis" and missing right parenthesis errors completely.

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