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');

Answer 1

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
);