Errors in compete table foreign key-CodePudding

I keep getting an error about my foreign key in the table 'compete'. I have been trying to look up what is wrong, but none of the examples seem to be my issue. Can anyone help me out? This is the error I keep getting:

ERROR 1005 (HY000): Can't create table `database`.`compete` (errno: 150 "Foreign key constraint is incorrectly formed")

Here is my code.

CREATE TABLE athlete (
        athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        country TINYTEXT NOT NULL,
        name TINYTEXT NOT NULL,
        birthdate DATE NOT NULL,
        age INT UNSIGNED NOT NULL,
        height_inch INT UNSIGNED,
        weight_lbs INT UNSIGNED,
        PRIMARY KEY (athlete_id)
    );
    
    CREATE TABLE sport (
        sport_id INT UNSIGNED NOT NULL,
        sport TINYTEXT NOT NULL,
        PRIMARY KEY (sport_id)
    );
    
    CREATE TABLE olympics (
        olympics_id INT UNSIGNED NOT NULL,
        season TINYTEXT NOT NULL,
        year YEAR NOT NULL,
        city TINYTEXT NOT NULL,
        PRIMARY KEY (olympics_id)
    );  
    
    CREATE TABLE sport_events (
        sport_id INT UNSIGNED NOT NULL,
        event_id INT UNSIGNED NOT NULL,
        event TINYTEXT NOT NULL,
        PRIMARY KEY (sport_id, event_id),
        FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
    );
    
    CREATE TABLE athlete_sport (
        athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        sport_id INT UNSIGNED NOT NULL,
        PRIMARY KEY (athlete_id),
        FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
        FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
    );
    
    CREATE TABLE compete (
        athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        olympics_id INT UNSIGNED NOT NULL,
        event_id INT UNSIGNED NOT NULL,
        medal TINYTEXT,
        PRIMARY KEY (athlete_id),
        FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
        FOREIGN KEY (olympics_id) REFERENCES olympics (olympics_id),
        FOREIGN KEY (event_id) REFERENCES sport_events (event_id)   
    );

CodePudding user response：

Drop and recreate the sport_events table, and use the event_id column as the first column of its primary key.

CREATE TABLE sport_events (
    sport_id INT UNSIGNED NOT NULL,
    event_id INT UNSIGNED NOT NULL,
    event TINYTEXT NOT NULL,
    PRIMARY KEY (event_id,sport_id),
    FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

CodePudding user response：

In sport_events table you have declared primary key with two columns. PRIMARY KEY (sport_id, event_id)

But in compete table you have added FOREIGN KEY (event_id) REFERENCES sport_events (event_id) with a single column.

That's why you get errno: 150 "Foreign key constraint is incorrectly formed" error.

Drop the complete table first and then sport_events table after that. Then change the primary key PRIMARY KEY (event_id) in sport_events table.

Or, simply switch PRIMARY KEY (event_id, sport_id). This will work.

After that create both tables.