Re: Can not add foreign key constraint

On 4/24/2017 15:28, Peter Brawley wrote:
> 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.

Ah, an error cascade, as Shawn Green noticed, there's a typo in my 
suggested command, should be ...

show engine innodb status;

PB

-----

>
> 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