Re: Can not add foreign key constraint
Hello, My thanks to everyone who helped on this issue. The index did it. Thanks. Dave. On 4/28/17, Johan De Meersman <vegiv...@tuxera.be> wrote: > That is quite different, as I suspected :-) > > Referential keys require an index on the target table that begins with the > referenced field, so you'll need to add one on user, as was specified in the > create table you originally posted. > > On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote: >>Hello, >> >> Here's the output of the command show create table virtual_users: >> >> create table virtual_users; >>+---+-+ >> | Table | Create Table >> >> >> >> >> >> >> | >>+---+-+ >> | virtual_users | CREATE TABLE `virtual_users` ( >> `id` int(11) NOT NULL AUTO_INCREMENT, >> `domain_id` int(11) NOT NULL, >> `user` varchar(40) NOT NULL, >> `password` varchar(128) NOT NULL, >> `quota` bigint(20) NOT NULL DEFAULT '0', >> `quota_messages` int(11) NOT NULL DEFAULT '0', >> PRIMARY KEY (`id`), >> UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), >> CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) >> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE >> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | >>+---+-+ >> 1 row in set (0.00 sec) >> >>Thanks. >>Dave. >> >>> >>> >>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote: >>>> >>>> I note that the innodb status says it couldn't find an index on the >>>> referenced column. Did the create statements come from your create >>>> scripts >>>> or from a show create table statement? I'm suspicious about the >>index on >>>> virtual_users(user). >>>> >>>> >>>> - Original Message - >>>>> From: "David Mehler" <dave.meh...@gmail.com> >>>>> To: "MySql" <mysql@lists.mysql.com> >>>>> Sent: Tuesday, 25 April, 2017 23:07:19 >>>>> Subject: Re: Can not add foreign key constraint >>>> >>>>> Hello, >>>>> >>>>> Tried recreating the virtual_users table didn't solve anything. >>Would >>>>> it be possible for anyone to check out my box directly? >>>>> >>>>> Thanks. >>>>> Dave. >>>> >>>> -- >>>> The bay-trees in our country are all wither'd >>>> And meteors fright the fixed stars of heaven; >>>> The pale-faced moon looks bloody on the earth >>>> And lean-look'd prophets whisper fearful change. >>>> These signs forerun the death or fall of kings. >>>> -- Wm. Shakespeare, "Richard II" >>>> >>> >>> Thanks. >>> Dave. >>> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/mysql > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > > -- > 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
Re: Can not add foreign key constraint
Hello, Here's the output of the command show create table virtual_users: create table virtual_users; +---+-+ | Table | Create Table | +---+-+ | virtual_users | CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT '0', `quota_messages` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.00 sec) Thanks. Dave. > > > On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote: >> >> I note that the innodb status says it couldn't find an index on the >> referenced column. Did the create statements come from your create >> scripts >> or from a show create table statement? I'm suspicious about the index on >> virtual_users(user). >> >> >> - Original Message - >>> From: "David Mehler" <dave.meh...@gmail.com> >>> To: "MySql" <mysql@lists.mysql.com> >>> Sent: Tuesday, 25 April, 2017 23:07:19 >>> Subject: Re: Can not add foreign key constraint >> >>> Hello, >>> >>> Tried recreating the virtual_users table didn't solve anything. Would >>> it be possible for anyone to check out my box directly? >>> >>> Thanks. >>> Dave. >> >> -- >> The bay-trees in our country are all wither'd >> And meteors fright the fixed stars of heaven; >> The pale-faced moon looks bloody on the earth >> And lean-look'd prophets whisper fearful change. >> These signs forerun the death or fall of kings. >> -- Wm. Shakespeare, "Richard II" >> > > Thanks. > Dave. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, Tried recreating the virtual_users table didn't solve anything. Would it be possible for anyone to check out my box directly? Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 20:47, David Mehler wrote: >> Hello, >> >> Thanks. Here's the create statements for virtual_domains, >> virtual_users, and the one that isn't working lastauth: >> >> CREATE TABLE `virtual_domains` ( >>`id` int(11) NOT NULL auto_increment, >>`name` varchar(50) NOT NULL, >>PRIMARY KEY (`id`) >> ) 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(128) 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; >> >> 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; > > Again, that works in 5.6 and 5.7, so if it doesn't work for you, there > is something wrong in one of your files. Did you try recreating > virtual_users? > > PB > > - > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> On 4/24/2017 18:16, David Mehler wrote: >>>> Hello, >>>> >>>> I'm running Mysql 5.7.18. >>>> >>>> My virtual_users are working fine, it's the new table that isn't. Or >>>> am I missing something? >>> MySQL says it cannot find an index which the table's create statement >>> declares. If that's the create statement that created the table, the >>> table's corrupted. >>> >>> PB >>> >>> >>>> Thanks. >>>> Dave. >>>> >>>> >>>> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>>>> On 4/24/2017 17:41, David Mehler wrote: >>>>>> Hello, >>>>>> >>>>>> Here's the output. I hope it helps. >>>>>> >>>
Re: Can not add foreign key constraint
Hello, Thanks. Here's the create statements for virtual_domains, virtual_users, and the one that isn't working lastauth: CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) 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(128) 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; 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; Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 18:16, David Mehler wrote: >> Hello, >> >> I'm running Mysql 5.7.18. >> >> My virtual_users are working fine, it's the new table that isn't. Or >> am I missing something? > > MySQL says it cannot find an index which the table's create statement > declares. If that's the create statement that created the table, the > table's corrupted. > > PB > > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> On 4/24/2017 17:41, David Mehler wrote: >>>> Hello, >>>> >>>> Here's the output. I hope it helps. >>>> >>>> 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
Re: Can not add foreign key constraint
Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 17:41, David Mehler wrote: >> Hello, >> >> Here's the output. I hope it helps. >> >> 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;
Re: Can not add foreign key constraint
--+ 1 row in set (0.01 sec) Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 15:28, Peter Brawley wrote: >> On 4/24/2017 13:59, David Mehler wrote: >>> Hello, >>> >>> root@localhost [mail]
Re: Can not add foreign key constraint
Hello, Here's the engines I have: root@localhost [(none)]> show engines; ++-++--+--++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-++--+--++ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV| YES | CSV storage engine | NO | NO | NO | | ARCHIVE| YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | ++-++--+--++ 9 rows in set (0.00 sec) Not sure why I'm getting the error 1286. Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> 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. > > 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,
Re: Can not add foreign key constraint
Hello, root@localhost [mail]> show engine innodb_status; ERROR 1286 (42000): Unknown storage engine 'innodb_status' 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
Re: Can not add foreign key constraint
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. Thanks. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Can not add foreign key constraints
Hello, I'm trying to add a table to an existing database. I'm wanting it to get one of it's fields from an already existing table. I've done this before in this database. This works: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) 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; This does not: 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; Can anyone spot the situation? Thanks. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
