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>