Web lists-archives.com

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 <vegivamp@xxxxxxxxx> 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.mehler@xxxxxxxxx> 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 <vegivamp@xxxxxxxxx> 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.mehler@xxxxxxxxx>
>>>>> To: "MySql" <mysql@xxxxxxxxxxxxxxx>
>>>>> 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