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

One more reason to add:

Different charset or engine will also cause this problem

Answer 2

This is'nt much a buggy characteristic of mysql. There can be two possible reasons for this! 1) the datatypes of the PK and the FK dont match. 2) This error can come up in versions prior to 4.1 where you need to explicitly define indexes.

Answer 3

Im my case its typically because of a datatype mismatch. Remember if it is an int check that both are either unsigned or not

Answer 4

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 5

In my person experience this a very buggy characteristic of MySQL - and the least hassle is to just dump the table structure and data, then drop the table and re-run the SQL from the dump and then manually re-create indexes and foreign key constraints where needed.

Answer 6

I had a constraint with the same name in a different table. Try changing the name of your constraint.