Error Code 1822. Failed to add the foreign key constraint

users9zV 0
Viewed 20

I'm trying to create these two tables on MySQL workbench and am unable to create the table 'Fee'. Error code 1822. What code would I need to add or modify to be able to create the two tables?

I've tried reading other threads and I did not understand their code(requiring indexing). I've just started learning SQL using mySQL.

create table RIDE(
    RIDE_NUMBER     INT     NOT NULL,
    RIDE_ADDRESS    VARCHAR(75)     NOT NULL,
    DRIVER_ID       VARCHAR(16)     NOT NULL,
    RIDER_ID        VARCHAR(16)     NOT NULL,
    DRIVER_VEHICLE  VARCHAR(32)     NOT NULL,
    RIDE_DURATION   TIME            NOT NULL,
    PRIMARY KEY (RIDE_NUMBER),
    CONSTRAINT RIDE_NUMBER_CONST UNIQUE(RIDE_NUMBER)
);

create table FEE(
    RIDE_DURATION   TIME            NOT NULL,
    SERVICE_FEE     DECIMAL(10,2)   NOT NULL,
    PRIMARY KEY (RIDE_DURATION),
    FOREIGN KEY (RIDE_DURATION) REFERENCES RIDE(RIDE_DURATION),
    CONSTRAINT SERVICE_CONST UNIQUE(RIDE_DURATION) /* considering the only factor that SERVICE_FEE is functionally dependent on is RIDE_DURATION. */
);
mysql sql
1 Answers

In MySQL official document for Foreign key ,we can find below description:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

In table FEE it has a foreign key reference RIDE(RIDE_DURATION),however RIDE_DURATION in table RIDE doesn't have a index on it,that's the reason!

In order to let the tables can be created success,you need to add an index for column RIDE(RIDE_DURATION) in table RIDE

create table RIDE(
    RIDE_NUMBER     INT     NOT NULL,
    RIDE_ADDRESS    VARCHAR(75)     NOT NULL,
    DRIVER_ID       VARCHAR(16)     NOT NULL,
    RIDER_ID        VARCHAR(16)     NOT NULL,
    DRIVER_VEHICLE  VARCHAR(32)     NOT NULL,
    RIDE_DURATION   TIME            NOT NULL,
    PRIMARY KEY (RIDE_NUMBER),
    INDEX RIDE_DURATION_INDEX (RIDE_DURATION), -- add an index for RIDE_DURATION
    CONSTRAINT RIDE_NUMBER_CONST UNIQUE(RIDE_NUMBER)
);

create table FEE(
    RIDE_DURATION   TIME            NOT NULL,
    SERVICE_FEE     DECIMAL(10,2)   NOT NULL,
    PRIMARY KEY (RIDE_DURATION),
    FOREIGN KEY (RIDE_DURATION) REFERENCES RIDE(RIDE_DURATION),
    CONSTRAINT SERVICE_CONST UNIQUE(RIDE_DURATION) /* considering the only factor that SERVICE_FEE is functionally dependent on is RIDE_DURATION. */
);

DB Fiddle Demo

lucumt0
Related