Skip site navigation (1)Skip section navigation (2)
Date:      Mon, 24 Apr 2017 09:29:14 +0000
From:      Gerard Seibert <carmel_ny@outlook.com>
To:        Matthew Seaman <matthew@FreeBSD.org>
Cc:        User questions <freebsd-questions@freebsd.org>
Subject:   Re: Can not add foreign key constraint
Message-ID:  <CY1PR20MB0363C9931FE907636ECE9F3E801F0@CY1PR20MB0363.namprd20.prod.outlook.com>
In-Reply-To: <ba1f1053-48db-d3c9-f543-087431a87721@FreeBSD.org>
References:  <CAPORhP4Y7JUn%2BZX6Dtju9KVdqLW11Mv0AddUTL%2BNhQMDOVf5og@mail.gmail.com> <ba1f1053-48db-d3c9-f543-087431a87721@FreeBSD.org>

next in thread | previous in thread | raw e-mail | index | archive | help
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:
> >=20
> > 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=3DInnoDB DEFAULT CHARSET=3Dutf8;
> >=20
> >=20
> > This does not:
> >=20
> > 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=3DInnoDB DEFAULT CHARSET=3Dutf8;
> >=20
> > Can anyone spot the situation? =20
>=20
> 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.
>=20
> 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

--=20
Carmel




Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?CY1PR20MB0363C9931FE907636ECE9F3E801F0>