Can not add foreign key constraint

[Gerard Seibert]

On Mon, 24 Apr 2017 08:31:19 +0100, Matthew Seaman stated:

> 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.

I have never used 'foreign keys' myself; however, I did find these two
articles regarding them;

http://www.mysqltutorial.org/mysql-foreign-key/
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

-- 
Carmel