CODEWITHSUNDEEP
sqloracle-databasedatabase-designoracle-sqldeveloperdatamodel
murplepunky
murplepunky

Reputation: 27

How to fix "ERROR: FK name length exceeds maximum allowed length(30)" in SQL Developer Data Modeller

I'm trying to turn the Logical Model of my Database into a DDL script, but I don't know how to fix this error in the DDL script or Data Modeller:-- ERROR: FK name length exceeds maximum allowed length(30) It seems to be based on my junction-table's Primary Key, which is made up from two Foreign Keys from the two neighboring tables.

I have tried changing the names of the Primary Keys in the neighboring tables, but when I try to generate a NEW DDL script with Data Modeler, it still generates the old script.

Here's the sections of code that create the 3 tables and link them together:

    CREATE TABLE items (
        item_no   NUMBER (8) NOT NULL,
        "year"      DATE,
        price     NUMBER (20,2)
    );

    ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY ( item_no );

    CREATE TABLE purchase_order (
        order_no            NUMBER(8) NOT NULL,
        quantity            INTEGER,
        item_description    VARCHAR2(200),
        unit_price          NUMBER(20,2),
        total               NUMBER(20,2),
        order_date          DATE,
        sales_person_code   VARCHAR2(5) NOT NULL,
        supplier_id         NUMBER(3) NOT NULL
    );

    ALTER TABLE purchase_order ADD CONSTRAINT purchase_order_pk PRIMARY KEY ( order_no );

    CREATE TABLE purchase_order_items (
        purchase_order_order_no   NUMBER(8) NOT NULL,
        items_item_no             NUMBER(8) NOT NULL
    );

    ALTER TABLE purchase_order_items ADD CONSTRAINT purchase_order_items_pk PRIMARY KEY ( items_item_no,
                                                                                          purchase_order_order_no );

    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_items_fk FOREIGN KEY ( items_item_no )
            REFERENCES items ( item_no );

    --  ERROR: FK name length exceeds maximum allowed length(30) 
    ALTER TABLE purchase_order_items
        ADD CONSTRAINT purchase_order_items_purchase_order_fk FOREIGN KEY ( purchase_order_order_no )
            REFERENCES purchase_order ( order_no );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_sales_person_fk FOREIGN KEY ( sales_person_code )
            REFERENCES sales_person ( code );

    ALTER TABLE purchase_order
        ADD CONSTRAINT purchase_order_supplier_fk FOREIGN KEY ( supplier_id )
            REFERENCES supplier ( id );

So I'm not sure exactly what FK name length is too long and what I need to change in the script to fix this error.

Upvotes: 2

Views: 9066

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Oracle limits identifiers to 30 characters, so purchase_order_items_purchase_order_fk needs to be shorted. Perhaps to something like poi_purchase_porder_fk.

Upvotes: 2

Related Questions