I must be having a brain fart today or something, I can't figure out why I can't make this foreign keys. My two create tables are below

CREATE TABLE IF NOT EXISTS `users` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username`    VARCHAR(10)      NOT NULL,
  `password`    CHAR(128)        NOT NULL,
  `unique_salt` CHAR(128)        NOT NULL,
  `lastlogin`   DATETIME         NOT NULL,
  `email`       VARCHAR(75)      NOT NULL,
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `games` (
  `id`      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `end`     DATETIME         NULL,
  `score`   INT              NULL,
  `letters` VARCHAR(15),
  `user_id` INT              NOT NULL,
  FOREIGN KEY fk_user_id(`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  `start`   DATETIME         NOT NULL,
  `timer`   INT              NOT NULL
) ENGINE = InnoDB;

I get back:

mysql> source /tmp/create_tables.sql
Query OK, 0 rows affected (0.02 sec)

ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 150
Message: Create table 'JumbleWords/games' with foreign key constraint failed. There is no index in the referenced table
where the referenced columns appear as the first columns.

*************************** 2. row ***************************
Level: Error
Code: 1215
Message: Cannot add foreign key constraint
2 rows in set (0.00 sec)

Most of my research shows that when using compound keys, the first column listed in that key must be what I'm trying to FK to. However, its not a compount key, I'm simply trying to point to the primary key of the users table. Any thoughts?

    NVM I'm a stupid ninny. My user_id in games was not unsigned. The warning / error messages could be a little more useful - something along the lines of "Foreign key constraint failed. The column definition does not match referenced column definition."

      Write a Reply...