Web lists-archives.com

Re: Can not add foreign key constraint




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