Can't create table `db`.`WRITER` (errno: 150 "Foreign key constraint is incorrectly formed")

Question

Hello I have an issue when trying inserting multiple foreign keys to a table. I have searched a lot of hours and still I don't figure it out.. It pops this error. I don't know what else I can do about that. Also I tried to add constraint .. foreign key ... references command and it didn't work.

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;

USE db;



CREATE TABLE BOOKS(
    Bno int not null primary key auto_increment,
    Title text,
    PDate date,
    Cno int,
    Cname text
);


CREATE TABLE AUTHORS(
    Ano int not null primary key auto_increment,
    Asurname text,
    Aname text
);


CREATE TABLE CATEGORIES(
    Cno int not null primary key auto_increment,
    Cname text,
    No_Of_Books int
);

CREATE TABLE SUMMARY_LANG(
    Bno int not null primary key auto_increment,
    Language text,
    FOREIGN KEY (Bno) REFERENCES BOOKS(Bno)
);

CREATE TABLE WRITER(
   Bno int,
   Ano int,
   Asurname text,
   Aname text,
   FOREIGN KEY (Bno) REFERENCES BOOKS(Bno),
   FOREIGN KEY (Ano) REFERENCES AUTHORS(Ano),
   FOREIGN KEY (Asurname) REFERENCES AUTHORS(Asurname),
   FOREIGN KEY (Aname) REFERENCES AUTHORS(Aname)
);

INSERT INTO BOOKS(Title,PDate,Cname)
VALUES
('A first course in database systems','2014-01-01','DATABASE'),
('FUNDAMENTAL CONCEPTS OF PROGRAMMING SYSTEMS','1976-01-01','PROGRAMMING');

ALTER TABLE AUTHORS auto_increment = 100;
INSERT INTO AUTHORS(Asurname,Aname)
VALUES
('ULLMAN','JEFF'),
('WIDOM','JENNIFER');

ALTER TABLE CATEGORIES auto_increment = 10;
INSERT INTO CATEGORIES(Cname, No_Of_Books)
VALUES
('DATABASE',1),
('PROGRAMMING',1);

INSERT INTO SUMMARY_LANG(Language)
VALUES
('ENG'),
('GRE'),
('ENG'),
('FRA');

Does this answer your question? [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) — Luuk, Feb 21 '22 at 15:36
[MySql, Is it a good practice to use 'text' datatype](https://stackoverflow.com/a/44493589/724039) — Luuk, Feb 21 '22 at 16:31

nbk · Accepted Answer · 2022-02-21T16:34:43.950

0

Your definition of SUMMARY_LANG is wrong

CREATE TABLE SUMMARY_LANG(
    Bno int not null primary key auto_increment,
    Language text,
    FOREIGN KEY (Bno) REFERENCES BOOKS(Bno)  <-- remove this reference
);

Remove the foreign key, because this is a Table that is used only as reference number to another table also called a helper table, because the text would be redundant in the referenced table.

But i can't see any column that references language.

So add a column to BOOKS, where you add the reference to SUMMARY_LANG and when you add new rows SUMMARY_LANG you won't get any errors anymore.

So the new tables can be like this

CREATE TABLE BOOKS (
    Bno INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Title TEXT,
    PDate DATE,
    Cno INT,
    Cname TEXT,
    SNno int,
    FOREIGN KEY (SNno)
        REFERENCES SUMMARY_LANG (SNno)
);
CREATE TABLE SUMMARY_LANG(
    SNno int not null primary key auto_increment,
    Language text
);

edited Feb 21 '22 at 16:34

answered Feb 21 '22 at 15:51

nbk

45,398
8
30
47

I edit my table and now I have this message `Can't create table `db`.`BOOKS` (errno: 150 "Foreign key constraint is incorrectly formed")` . Did you mean to create a foreign key in your last sentence ? `CREATE TABLE BOOKS( Bno int not null primary key auto_increment, Title text, PDate date, Cno int, Cname text, Language text, FOREIGN KEY(Language) REFERENCES SUMMARY_LANG(Language));` @nbk – Cheat3d Feb 21 '22 at 16:01
i added the correct syntax – nbk Feb 21 '22 at 16:35
thanks for helping me but still i have the same issue.. i copy and paste your solution but it didn't work @nbk – Cheat3d Feb 22 '22 at 10:19
it works just fine https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fdea13195740b1ad405eb3c572d0c84a – nbk Feb 22 '22 at 10:47
I tried it with datagrip it didn't work.. anyways thank you very much dude @nbk – Cheat3d Feb 23 '22 at 11:07
check the dbfiddle and you need the correct sequence, If you get an error , show me your own fiddle and i will see what i can do – nbk Feb 23 '22 at 12:51
it's not from dbfiddle but from my datagrip but anyways maybe it's a bug from sql. Because I had a lot of issues with foreign keys and on other computers they were working fine. @nbk – Cheat3d Feb 23 '22 at 20:23

Can't create table `db`.`WRITER` (errno: 150 "Foreign key constraint is incorrectly formed")

1 Answers1