Re: Can not add foreign key constraint

On 4/24/2017 13:59, David Mehler wrote:
> Hello,
>
> root@localhost [mail]> show engine innodb_status;
> ERROR 1286 (42000): Unknown storage engine 'innodb_status'

Well it's very unlikely InnoDB made that up, it's probably in one of 
your Create texts.

PB

-----

>
>
> This is on a Mysql 5.7 setup.
>
> Thanks.
> Dave.
>
>
>
> On 4/24/17, Peter Brawley <[email protected]> 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