All geek questions in one place

MySQL: unable to create table (errno: 150)

I am working on my first MySQL database for a job at my university. Unfortunately, I was stuck for some time trying to create tables with foreign keys between them.

This is the error that the MySQL Workbench Technical Support Wizard gives:

Executing SQL script on server

ERROR: Error 1005: Can't create table 'test.fremført' (errno: 150) CREATE TABLE IF NOT EXISTS `Fremført` ( `Plate` VARCHAR(20) NOT NULL , `Verk` VARCHAR(45) NOT NULL , `Artist` VARCHAR(45) NOT NULL , `Dato` DATE NULL , PRIMARY KEY (`Plate`, `Verk`, `Artist`) , INDEX `Fremført->Artist_idx` (`Artist` ASC) , INDEX `Fremført->Spor_idx` (`Plate` ASC, `Verk` ASC) , CONSTRAINT `Fremført->Artist` FOREIGN KEY (`Artist` ) REFERENCES `Artist` (`ArtistNavn` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Fremført->Spor` FOREIGN KEY (`Plate` , `Verk` ) REFERENCES `Spor` (`Verk` , `Verk` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB

Does anyone know what is wrong with the above script, and if so, is there a solution?

Thanks!

edit: This is the requested spor request

 DROP TABLE IF EXISTS `Spor` ; CREATE TABLE IF NOT EXISTS `Spor` ( `Plate` VARCHAR(45) NOT NULL , `Verk` VARCHAR(45) NOT NULL , `Spilletid` DECIMAL(3,2) NULL , PRIMARY KEY (`Plate`, `Verk`) , INDEX `Plate_idx` (`Plate` ASC) , CONSTRAINT `Plate` FOREIGN KEY (`Plate` ) REFERENCES `Plate` (`KatalogNr` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; DROP TABLE IF EXISTS `Artist` ; CREATE TABLE IF NOT EXISTS `Artist` ( `ArtistNavn` VARCHAR(30) NOT NULL , `Artistcol` VARCHAR(45) NULL , PRIMARY KEY (`ArtistNavn`) ) ENGINE = InnoDB;
+4
source share
3 answers

errno150 is very often associated with a mismatch between the data types of the primary and associated columns. They must match exactly, including the length of the character.

I see a data type mismatch between Fremført.artist ( VARCHAR(45) ) and Artist.ArtistNavn ( VARCHAR(30) ). They must be the same for the FOREIGN KEY constraint.

 CREATE TABLE IF NOT EXISTS `Fremført` ( `Plate` VARCHAR(20) NOT NULL , `Verk` VARCHAR(45) NOT NULL , /* Must match the primary table VARCHAR(30) */ `Artist` VARCHAR(30) NOT NULL , `Dato` DATE NULL , PRIMARY KEY (`Plate`, `Verk`, `Artist`) , INDEX `Fremført->Artist_idx` (`Artist` ASC) , INDEX `Fremført->Spor_idx` (`Plate` ASC, `Verk` ASC) , CONSTRAINT `Fremført->Artist` FOREIGN KEY (`Artist` ) REFERENCES `Artist` (`ArtistNavn` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Fremført->Spor` FOREIGN KEY (`Plate` , `Verk` ) /* Was this intentional, rather than (`Plate`, `Verk`)? */ /* If not, you must also match the data type of `Plate` VARCHAR(20) to that of Spor.Plate VARCHAR(45) */ REFERENCES `Spor` (`Verk` , `Verk` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB

As noted above, if the Fremført->Spor constraint was intended to reference Spor (Plate , Verk ) instead of Spor (Verk , Verk ) , as you defined it, then you will also encounter err150 due to type mismatch between Spor.Plate and Fremført.Plate . Change Fremført.Plate to VARCHAR(45) .

+3
source

Error 150 - foreign key constraint issue. I suspect one of the two FKs you have a problem.

See: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

0
source

(errno: 150) This error means that FK could not create the create table ....

Please view your FK.

  • Have you already created it? (table with PK indicating this)
  • Is your spell right?
  • Is this the same data type as PK?
0
source

Source: https://habr.com/ru/post/1439003/

More articles:


All Articles