Can not add foreign key constraint

[Matthew Seaman]

On 24/04/2017 08:13, David Mehler wrote:
> Not sure if this is a FreeBSD issue specific with the Mysql port 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?

This is a question better put on one of the MySQL support sites -- it's
not really anything FreeBSD related.  I do know mysql has a somewhat
lacking approach to foreign keys -- it's only with InnoDB recently that
it's had anything like reasonable support, and even now it allows but
ignores one of the two standard ways of adding a foreign key in a table
declaration.

Try using an integer field as the foreign key -- the primary key of the
referenced table is generally the best choice.

	Cheers,

	Matthew


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 931 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20170424/458e7787/attachment.sig>