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