0

I'm trying to set up a reference between two tables, and I keep running into this error when I load the page.

Error creating table: Cannot add foreign key constraint

This is the 'calendars' table: calendars table image

And this is the current query I am running to create the table:

CREATE TABLE `'.$classTableName.'`(
    classID INT(11) NOT NULL,
    name VARCHAR(255) NOT NULL,
    users INT(11) NOT NULL,
    files INT(11) NOT NULL,
    chats INT(11) NOT NULL,
    deadlines INT(11) NOT NULL,
    calendar INT(11) NOT NULL,
    PRIMARY KEY (classID), 
    FOREIGN KEY (calendar) REFERENCES calendars(classID)
) ENGINE=INNODB;

Any help would be appreciated! :)

joshkeley
  • 1
  • 1
  • Do you have an index on `calendars.classID`? The foreign key has to point to a column with an index. Usually it points to the primary key of the table. – Barmar Feb 12 '16 at 06:27
  • Instead of the image, show the output of `SHOW CREATE TABLE calendars`. – Barmar Feb 12 '16 at 06:28
  • Adding a primary index on calendars.classID has solved it, thanks so much! – joshkeley Feb 12 '16 at 06:37
  • This should be closed as a duplicate of http://stackoverflow.com/questions/1721841/does-mysql-innodb-always-require-an-index-for-each-foreign-key-constraint – Barmar Feb 12 '16 at 06:39

1 Answers1

0

Had to index calendars.classID, as pointed out by Basmar.

joshkeley
  • 1
  • 1