Web lists-archives.com

Re: Can not add foreign key constraint




Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley <peter.brawley@xxxxxxxxxxxxx> wrote:
> 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
>
>

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