MySQL error 150, cannot create table.

Question

I'm having trouble creating a table and I don't understand what's wrong. phpMyAdmin sets the error indicator next to the PRIMARY KEY declaration... I don't get why this is wrong...

This table is a child table, which has a one-to-many identifying relationship with another table.

CREATE TABLE IF NOT EXISTS `ruilen`.`Voorwerpen` (
`voorwerpen_id` INT NOT NULL AUTO_INCREMENT ,
`naam` VARCHAR( 45 ) NOT NULL ,
`beschrijving` VARCHAR( 45 ) NULL ,
`Gebruikers_gebruiker_id` INT NOT NULL ,
PRIMARY KEY ( `voorwerpen_id` , `Gebruikers_gebruiker_id` ) ,
CONSTRAINT `fk_Voorwerpen_Gebruikers1` FOREIGN KEY ( `Gebruikers_gebruiker_id` ) REFERENCES `ruilen`.`Gebruikers` (
`gebruiker_id`
) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB;

MySQL said: Documentation
#1005 - Can't create table 'ruilen.voorwerpen' (errno: 150)

EDIT: this is all the documentation on the error code I can find: Link

EDIT2: pic removed

EDIT3:

CREATE TABLE `gebruikers` (
 `gebruiker_id` int(11) NOT NULL,
 `naam` varchar(45) NOT NULL,
 `straat` varchar(45) NOT NULL,
 `gemeente` varchar(45) NOT NULL,
 `mail` varchar(45) NOT NULL,
 `beschrijving` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`gebruiker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

Check that Gebruikers_gebruiker_id and Gebruikers.gebruiker_id have same datatype.

Also check that Gebruikers.gebruiker_id is a PRIMARY KEY in Gebruikers

Update:

You have ON DELETE SET NULL defined, while your Gebruikers_gebruiker_id is defined as NOT NULL.

Fix it (change to ON DELETE CASCADE or just remove the clause) and you'll be able to create the reference.

Answer 2

Is the other table InnoDB as well?