Re: Can not add foreign key constraint

On 4/24/2017 2:10 PM, Peter Brawley wrote:
> On 4/24/2017 12:28, David Mehler wrote:
>> ...snip
>
> 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
>

Typo warning:   "innodb status" is two words (no underscore)

https://dev.mysql.com/doc/refman/5.6/en/show-engine.html

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

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