Re: Can not add foreign key constraint

[David Mehler]

Hello,

My thanks to everyone who helped on this issue. The index did it.

Thanks.
Dave.


On 4/28/17, Johan De Meersman  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  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  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" 
> To: "MySql" 
> 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

[Johan De Meersman]

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  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  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" 
 To: "MySql" 
 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