Web lists-archives.com

Re: Can not add foreign key constraint




On 4/24/2017 12:28, David Mehler wrote:
Hello,

   Here's the create table sand error message.

   root@localhost [(none)]> use mail;
   Database changed
   root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
       -> `user` varchar(40) NOT NULL,
       -> `remote_ip` varchar(18) NOT NULL,
       -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
   UPDATE CURRENT_TIMESTAMP,
       -> PRIMARY KEY (`user`),
       -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
       -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   ERROR 1215 (HY000): Cannot add foreign key constraint


   For the table it's referencing here it is:

   CREATE TABLE `virtual_users` (
     `id` int(11) NOT NULL auto_increment,
     `domain_id` int(11) NOT NULL,
     `user` varchar(40) NOT NULL,
    `password` varchar(32) NOT NULL,
    `quota` bigint(20) NOT NULL DEFAULT 256,
     `quota_messages` int(11) NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`),
     UNIQUE KEY `user` (`user`),
     FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Hope this helps.

Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

  id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

  `id` int(11) NOT NULL auto_increment,

  `domain_id` int(11) NOT NULL,

  `user` varchar(40) NOT NULL,

  `password` varchar(32) NOT NULL,

  `quota` bigint(20) NOT NULL DEFAULT 256,

  `quota_messages` int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`),

  UNIQUE KEY (`user`),

  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

  `user` varchar(40) NOT NULL,

  `remote_ip` varchar(18) NOT NULL,

  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`user`),

  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql