Avatar of ltpitt
ltpitt asked on

Porting tiny database from PostgreSQL to MySQL

Hello there,

I have this working db in PostgreSQL:

DROP DATABASE IF EXISTS clanwars;

CREATE DATABASE clanwars;

USE clanwars;

CREATE TABLE t_teams (

    id SERIAL PRIMARY KEY,
    name TEXT,
    region TEXT,
    logo_url TEXT

);



CREATE TABLE t_matches (

    id SERIAL PRIMARY KEY,
    id_team_a INTEGER,
    id_team_b INTEGER,
    score_team_a INTEGER,
    score_team_b INTEGER,
    date DATE,

    constraint fk_team_a_id
    foreign key(id_team_a) references t_teams(id),

    constraint fk_team_b_id
    foreign key(id_team_b) references t_teams(id)

    );

Open in new window


If I try to create it in MySQL I get:
#1005 - Can't create table 'clanwars.t_matches' (errno: 150)

What am I doing wrong?
DatabasesPostgreSQLMySQL Server

Avatar of undefined
Last Comment
ltpitt

8/22/2022 - Mon
Russell Fox

Try naming your foreign key constraints:
DROP DATABASE IF EXISTS clanwars;

CREATE DATABASE clanwars;

USE clanwars;

CREATE TABLE t_teams (

    id SERIAL PRIMARY KEY,
    name TEXT,
    region TEXT,
    logo_url TEXT

);



CREATE TABLE t_matches (

    id SERIAL PRIMARY KEY,
    id_team_a INTEGER,
    id_team_b INTEGER,
    score_team_a INTEGER,
    score_team_b INTEGER,
    date DATE,

    constraint fk_team_a_id
    foreign KEY 'fk_id_team_a' (id_team_a) references t_teams(id),

    constraint fk_team_b_id
    foreign KEY 'fk_id_team_b' (id_team_b) references t_teams(id)

    );

Open in new window

ASKER CERTIFIED SOLUTION
ltpitt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ltpitt

This was the correct solution to my problem
Your help has saved me hundreds of hours of internet surfing.
fblack61