Databases
StackOverflow2013
Postsdbo
POMYSQL Foreign Key errno: 150 cannot create tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL Foreign Key errno: 150 cannot create tables
    primarykey
    data
    text
    <p>I've been reading around StackOverflow and various forums about this problem but I cannot seem to figure it out. When trying to run the "CREATE TABLE Class" and "CREATE TABLE Enroll" commands below I get "ERROR 1005: Can't create table university.class (errno: 150)". I am using InnoDB as my storage engine. The first two "CREATE" statements work fine. </p> <p>What changes do I need to make so that the "CREATE TABLE Class and CREATE TABLE Enroll" sections work?</p> <pre><code>CREATE TABLE Student ( stuId VARCHAR(6), lastName VARCHAR(20) NOT NULL, firstName VARCHAR(20) NOT NULL, major VARCHAR(10), credits FLOAT(3) DEFAULT 0, CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId), CONSTRAINT Student_credits_cc CHECK ((credits&gt;=0) AND (credits &lt; 150))); CREATE TABLE Faculty ( facId VARCHAR(6), name VARCHAR(20) NOT NULL, department VARCHAR(20), rank VARCHAR(10), CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId)); CREATE TABLE Class ( classNumber VARCHAR(8), facId VARCHAR(6) NOT NULL, schedule VARCHAR(8), room VARCHAR(6), CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE SET NULL, CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room)); CREATE TABLE Enroll ( stuId VARCHAR(6), classNumber VARCHAR(8), grade VARCHAR(2), CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId), CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) ON DELETE CASCADE, CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student (stuId)ON DELETE CASCADE); </code></pre> <p>Here is the full command and error:</p> <pre><code>mysql&gt; CREATE TABLE Class (classNumber VARCHAR(8), facId VARCHAR(6) NOT NULL, schedule VARCHAR(8), room VARCHAR(6), CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE SET NULL, CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room)); ERROR 1005 (HY000): Can't create table 'university.Class' (errno: 150) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. UStbenz9
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCould it be because facId in table 'class' is set to NOT NULL but in table 'Faculty' facId is not?
      singulars
      1. POMYSQL Foreign Key errno: 150 cannot create tables
      1. USandrewb
    2. COI tried adding "NOT NULL" to facId in the Faculty table and it made no difference in the other two commands. Thanks for the idea but unfortunately it didn't solve my problem.
      singulars
      1. POMYSQL Foreign Key errno: 150 cannot create tables
      1. UStbenz9
    3. COpossible duplicate : [Mysql create table with multiple foreign key on delete set null](http://stackoverflow.com/questions/15880070/mysql-create-table-with-multiple-foreign-key-on-delete-set-null)
      singulars
      1. POMYSQL Foreign Key errno: 150 cannot create tables
      1. USaaron
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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