Note that there are some explanatory texts on larger screens.

plurals
  1. POInnoDB CREATE TABLE throwing error 150
    primarykey
    data
    text
    <p>This create table is throwing an error 150:</p> <pre><code>CREATE TABLE IF NOT EXISTS published( isbn VARCHAR(13) NOT NULL, publisherid INTEGER NOT NULL, year INTEGER NOT NULL, lastupdate TIMESTAMP NOT NULL, lastupdateby INTEGER NOT NULL, FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid), FOREIGN KEY (isbn) REFERENCES books, FOREIGN KEY (publisherid) REFERENCES publishers, PRIMARY KEY (isbn, publisherid) ) ENGINE = INNODB; </code></pre> <p>I've checked that the tables it references (<code>librarians</code>, <code>books</code>, and <code>publishers</code>) are already created when SQL gets to this bit. Here are their DDLs:</p> <pre><code>CREATE TABLE IF NOT EXISTS librarians( librarianid INTEGER AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, canread BOOLEAN NOT NULL, canwrite BOOLEAN NOT NULL, canexec BOOLEAN NOT NULL, lastupdate TIMESTAMP NOT NULL, lastupdateby INTEGER NOT NULL, FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid) ) ENGINE = INNODB; CREATE TABLE IF NOT EXISTS books( isbn VARCHAR(13) PRIMARY KEY, title VARCHAR(255) NOT NULL, lastupdate TIMESTAMP NOT NULL, lastupdateby INTEGER NOT NULL, FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid) ) ENGINE = INNODB; CREATE TABLE IF NOT EXISTS publishers( publisherid INTEGER PRIMARY KEY AUTO_INCREMENT, publishername VARCHAR(255) UNIQUE NOT NULL, lastupdate TIMESTAMP NOT NULL, lastupdateby INTEGER NOT NULL, FOREIGN KEY (lastupdateby) REFERENCES librarians (librarianid) ) ENGINE = INNODB; </code></pre> <p>Further, I've checked the following guidelines I found <a href="https://stackoverflow.com/a/380074">here</a>:</p> <p><strong>The referenced tables should be InnoDB as well.</strong> Well, I even had that explicitly specified.</p> <p><strong>Referenced tables must have an index and primary key.</strong> The foreign key fields are the (sole) primary keys of their respective reference tables. Unless I misunderstand this requirement, that should fulfill it.</p> <p><strong>SQL data types of FK column and referenced PK column must be identical.</strong> Unless I checked it wrong, this, too should be okay.</p> <ul> <li><code>lastupdateby INTEGER NOT NULL</code> --> <code>librarianid INTEGER AUTO_INCREMENT PRIMARY KEY</code></li> <li><code>isbn VARCHAR(13) NOT NULL</code> --> <code>isbn VARCHAR(13) PRIMARY KEY</code></li> <li><code>publisherid INTEGER NOT NULL</code> --> <code>publisherid INTEGER PRIMARY KEY AUTO_INCREMENT</code></li> </ul> <p>So, what did I miss? What can be the cause of this error 150?</p>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload