Web lists-archives.com

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.brawley@xxxxxxxxxxxxx> 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.brawley@xxxxxxxxxxxxx> 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
>
>

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