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
