如何在SQL Developer中创建多表？请求修复建表语句报错问题

I’ve gone through your DDL statements and spotted several issues that are blocking your table creation. Let’s work through fixing them step by step:

Key Problems Found #

• Circular Foreign Key Loop: Your ACCOUNT and DRIVER tables reference each other’s primary keys. Oracle can’t create tables in this scenario—each table needs the other to exist first, creating an unresolvable dependency.
• Invalid Table Reference: The RECORD table tries to link to SERVER2, but your actual server table is named SERVER.
• Mismatched Column Data Types: For example, RECORD.SERVER_ID is defined as VARCHAR2(25) while SERVER.SERVER_ID is VARCHAR2(20)—foreign key columns must match exactly in data type and length.
• Broken NUMBER Syntax: Columns like V_MILEAGE NUMBER (,0) use invalid syntax; the correct format is NUMBER(*, 0) (the * denotes maximum precision).
• Inconsistent Schema Prefix: The DRIVER table uses SO_MODEL.DRIVER while other tables don’t specify a schema, which can cause permission or table resolution errors.
• Inconsistent VARCHAR2 Syntax: Mixing VARCHAR2(25) and VARCHAR2(25 BYTE)—while functionally similar in default settings, keeping syntax consistent avoids unexpected issues.

Corrected DDL Statements #

I’ve reorganized the creation order to resolve dependencies, fixed syntax errors, and addressed the circular dependency (assuming your business logic allows a driver to have multiple accounts—adjust this if you need a strict one-to-one relationship):

-- 1. Create SERVER first (no dependencies)
CREATE TABLE SERVER (
    SERVER_ID VARCHAR2(20) NOT NULL,
    SERVER_IP VARCHAR2(15) NOT NULL,
    SERVER_LOCATION VARCHAR2(20) NOT NULL,
    SERVER_BRAND VARCHAR2(15) NOT NULL,
    CONSTRAINT SERVER_PK PRIMARY KEY (SERVER_ID)
);

-- 2. Create DRIVER next (removed circular FK temporarily)
CREATE TABLE SO_MODEL.DRIVER (
    DRIVER_ID VARCHAR2(25) NOT NULL,
    LICENSE_NUM VARCHAR2(25) NOT NULL,
    FIRST_NAME VARCHAR2(25) NOT NULL,
    LAST_NAME VARCHAR2(25) NOT NULL,
    INSURANCE_POLICY VARCHAR2(25) NOT NULL,
    CONSTRAINT DRIVER_PK PRIMARY KEY (DRIVER_ID)
);

-- 3. Create ACCOUNT (depends on DRIVER)
CREATE TABLE ACCOUNT (
    ACCOUNT_NUM NUMBER(*, 0) NOT NULL,
    DRIVER_ID VARCHAR2(25) NOT NULL,
    DEVICE_ID VARCHAR2(25) NOT NULL,
    DATE_CREATED DATE NOT NULL,
    ACCOUNT_STATUS VARCHAR2(10) NOT NULL,
    CONSTRAINT ACCOUNT_PK PRIMARY KEY (ACCOUNT_NUM),
    CONSTRAINT ACCOUNT_FK1 FOREIGN KEY (DRIVER_ID) REFERENCES SO_MODEL.DRIVER (DRIVER_ID)
);

-- 4. Add ACCOUNT_NUM to DRIVER and link the FK (if you need the reverse relationship)
ALTER TABLE SO_MODEL.DRIVER
ADD ACCOUNT_NUM NUMBER(*, 0) NOT NULL;

ALTER TABLE SO_MODEL.DRIVER
ADD CONSTRAINT DRIVER_FK1 FOREIGN KEY (ACCOUNT_NUM) REFERENCES ACCOUNT (ACCOUNT_NUM);

-- 5. Create VEHICLE (depends on DRIVER)
CREATE TABLE VEHICLE (
    VIN_NUM VARCHAR2(25) NOT NULL,
    V_MILEAGE NUMBER(*, 0) NOT NULL,
    V_GASUSED VARCHAR2(25) NOT NULL,
    V_ELECTRICALMILES NUMBER(*, 0) NOT NULL,
    DRIVER_ID VARCHAR2(25) NOT NULL,
    CONSTRAINT VEHICLE_PK PRIMARY KEY (VIN_NUM),
    CONSTRAINT VEHICLE_FK1 FOREIGN KEY (DRIVER_ID) REFERENCES SO_MODEL.DRIVER (DRIVER_ID)
);

-- 6. Create RECORD (depends on VEHICLE and SERVER)
CREATE TABLE RECORD (
    RECORD_ID VARCHAR2(25) NOT NULL,
    VIN_NUM VARCHAR2(25) NOT NULL,
    SERVER_ID VARCHAR2(20) NOT NULL, -- Matched length to SERVER.SERVER_ID
    CONSTRAINT RECORD_PK PRIMARY KEY (RECORD_ID),
    CONSTRAINT RECORD_FK1 FOREIGN KEY (VIN_NUM) REFERENCES VEHICLE (VIN_NUM),
    CONSTRAINT RECORD_FK2 FOREIGN KEY (SERVER_ID) REFERENCES SERVER (SERVER_ID) -- Fixed reference to SERVER
);

-- 7. Create APPLICATION (depends on ACCOUNT and RECORD)
CREATE TABLE APPLICATION (
    APP_ID VARCHAR2(25) NOT NULL,
    ACCOUNT_NUM INT NOT NULL,
    RECORD_ID VARCHAR2(10) NOT NULL,
    VERSION_ID VARCHAR2(10) NOT NULL,
    LAST_UPDATE DATE NOT NULL,
    CONSTRAINT APPLICATION_PK PRIMARY KEY (APP_ID),
    CONSTRAINT APPLICATION_FK1 FOREIGN KEY (ACCOUNT_NUM) REFERENCES ACCOUNT (ACCOUNT_NUM),
    CONSTRAINT APPLICATION_FK2 FOREIGN KEY (RECORD_ID) REFERENCES RECORD (RECORD_ID)
);

Quick Notes #

• If you need a strict one-to-one relationship between ACCOUNT and DRIVER, you could instead use one table’s primary key as the other’s (e.g., make ACCOUNT_NUM the primary key of both tables) or add a unique constraint to the foreign key column.
• If you don’t intend to use the SO_MODEL schema for DRIVER, remove that prefix to match the other tables.

内容的提问来源于stack exchange，提问作者이지민