Executing SQL script in server ERROR: Error 1005: Can't create table (errno: 150)

Question

I am having a problem on the creation of my table.

The problem lays with the creation of the foreign key/relation to antoher table.

The internet told me to check the column type and check if it was the same as the column which i was referencing to. and its the same.

Futher solutions the internet gave me didn't work So can anyone please help me?

This is the query :

CREATE  TABLE IF NOT EXISTS `finprodb`.`tblproject` (
  `prj_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `prj_nummer` VARCHAR(45) NOT NULL ,
  `prj_omschrijving` TEXT NULL DEFAULT NULL ,
  `prj_verkoop_waarde` DECIMAL(20,4) NULL DEFAULT NULL ,
  `prj_gereed` TINYINT(4) NULL DEFAULT NULL ,
  `prj_bedr_id` INT(11) NOT NULL ,
  PRIMARY KEY (`prj_id`) ,
  UNIQUE INDEX `prj_id_UNIQUE` (`prj_id` ASC) ,
  INDEX `fk_tblproject_tblbedrijf1_idx` (`prj_bedr_id` ASC) ,
  CONSTRAINT `fk_tblproject_tblbedrijf1`
    FOREIGN KEY (`prj_bedr_id` )
    REFERENCES `finprodb`.`tblbedrijf` (`bedr_id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

If you have enough privileges, you can get the exact details by issuing a SHOW ENGINE innodb STATUS query. — Álvaro González
– Álvaro González, Commented Mar 7, 2013 at 9:18

fancyPants · Accepted Answer · 2013-03-07 09:15:31Z

1

ON DELETE SET NULL does not go well with prj_bedr_id INT(11) NOT NULL

Make the column nullable (remove the NOT)

answered Mar 7, 2013 at 9:15

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WillemT Over a year ago

Thank you very much. that was the solution. I will mark the answer as the solution in a few minutes

Graham Saint · Accepted Answer · 2013-03-07 09:18:25Z

0

I don't know MYSQL well but in DB2 you can't create a relationship unless there is a unique index on the columns referenced in the parent table. Is there a unique index on finprodb.tblbedrijf (bedr_id ) ?

answered Mar 7, 2013 at 9:18

Graham Saint

351 silver badge4 bronze badges

Collectives™ on Stack Overflow

Executing SQL script in server ERROR: Error 1005: Can't create table (errno: 150)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related