CODEWITHSUNDEEP
mysqlsqlforeign-keys
Taerus
Taerus

Reputation: 475

Cannot create interconnecting table

i'm trying to create a table with columns that reference toward other tables. How do i make the foreign keys?

Scheme: enter image description here

Query: (not working):

CREATE TABLE gebruikers_trainingen (
    gebruiker_id INT UNSIGNED NOT NULL,
    training_id INT UNSIGNED NOT NULL,
    gebruiker_naam VARCHAR(255) NOT NULL,
    training_naam VARCHAR(255),

    CONSTRAINT fk_idGebruiker FOREIGN KEY (gebruiker_id)
    REFERENCES gebruikers(id),
    CONSTRAINT fk_idTraining FOREIGN KEY (training_id)
    REFERENCES trainingen(id),
    CONSTRAINT fk_naamGebruiker FOREIGN KEY (gebruiker_naam)
    REFERENCES gebruikers(voornaam),
    CONSTRAINT fk_naamTraining FOREIGN KEY (training_naam)
    REFERENCES trainingen(naam)
) ENGINE = INNODB;

Getting:

Error Code: 1005 Can't create table 'konecranes.gebruikers_trainingen' (errno: 150)

EDIT: Other tables' queries.

CREATE TABLE gebruikers (
    id int unsigned NOT NULL,
    voornaam varchar(255) NOT NULL,
    achternaam varchar(255) NOT NULL,
    account_level int unsigned NOT NULL,
    PRIMARY KEY (id, voornaam)
) ENGINE = InnoDB;

CREATE TABLE trainingen (
    id int unsigned NOT NULL,
    naam varchar(255) NOT NULL,
    PRIMARY KEY (id, naam)
) ENGINE = InnoDB;

Upvotes: 0

Views: 63

Answers (3)

Justin Iurman
Justin Iurman

Reputation: 19016

You should add indexes on your foreign keys:

CREATE TABLE gebruikers_trainingen (
    gebruiker_id INT UNSIGNED NOT NULL,
    training_id INT UNSIGNED NOT NULL,
    gebruiker_naam VARCHAR(255) NOT NULL,
    training_naam VARCHAR(255) NOT NULL,

    INDEX (gebruiker_id, gebruiker_naam),
    INDEX (training_id, training_naam),

    CONSTRAINT fk_idGebruiker FOREIGN KEY (gebruiker_id, gebruiker_naam) 
    REFERENCES gebruikers(id, voornaam),

    CONSTRAINT fk_idTraining FOREIGN KEY (training_id, training_naam) 
    REFERENCES trainingen(id, naam)
) ENGINE = INNODB;

Upvotes: 1

Taerus
Taerus

Reputation: 475

Merging the constraints as follows did work it out. Also thanks too Justin Lurman for helping me out, had to add Indexes aswell.

CONSTRAINT fk_gebruikers FOREIGN KEY (gebruiker_id, gebruiker_naam) REFERENCES gebruikers(id, voornaam),
CONSTRAINT fk_trainingen FOREIGN KEY (training_id, training_naam) REFERENCES trainingen(id, naam)

Upvotes: 0

user844705
user844705

Reputation:

Has this table existed before in a different guise?

Mysql 1005 error when creating table using InnoDB engine

Hth Oli

Upvotes: 0

Related Questions