CODEWITHSUNDEEP
mysqlsqlmariadb
NTG-A
NTG-A

Reputation: 13

Foreign key constraint is incorrectly formed (MariaDB)

I'm just trying to execute a MySQL file within MariaDB, but it gives me the following error: ERROR 1005 (HY000) at line 14: Can't create table roundcube.calendars (errno: 150 "Foreign key constraint is incorrectly formed")

That's the SQL query: https://pastebin.com/4FBA30JM Unfortunately I can't post it here since it kinda messes up the formatting.

Upvotes: 1

Views: 1849

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

Here's your calendars table definition:

CREATE TABLE IF NOT EXISTS `calendars` (
  `calendar_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `color` varchar(8) NOT NULL,
  `showalarms` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY(`calendar_id`),
  INDEX `user_name_idx` (`user_id`, `name`),
  CONSTRAINT `fk_calendars_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;

I tested this out in a sandbox and the statement is fine. There's nothing wrong with it.

However, I don't see a users table in your linked code sample. Before you can create a table with a foreign key, the following must be true:

  • The referenced table (users in your case) must exist.
  • The referenced table must use the InnoDB storage engine.
  • The referenced column (user_id) must exist in the table.
  • The referenced column must have exactly the same data type as the foreign key column that references it. In your case, this is INT UNSIGNED (The integer length argument (10) is optional and may be different in the two tables).
  • The referenced column must be the leftmost column of a KEY. Ideally it should be the entire PRIMARY KEY or UNIQUE KEY, to be compatible with standard SQL. Technically, InnoDB also allows foreign keys to reference non-unique keys, but this is discouraged.

So you must have a table at least like the following already existing in your database:

CREATE TABLE `users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;

I had to create this table in my sandbox before your calendars table definition would work.

Upvotes: 2

Related Questions