Link to home
Get AccessLog in
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?
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

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
Avatar of ltpitt
ltpitt

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of ltpitt
ltpitt

ASKER

This was the correct solution to my problem