Re: Can not add foreign key constraint

[Peter Brawley]

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 <peter.braw...@earthlink.net> 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