We help IT Professionals succeed at work.

Porting tiny database from PostgreSQL to MySQL

ltpitt
ltpitt asked
on
223 Views
Last Modified: 2016-02-17
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?
Comment
Watch Question

Russell FoxDatabase Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This was the correct solution to my problem
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.