Date: Mon, 24 Apr 2017 08:31:19 +0100 From: Matthew Seaman <matthew@FreeBSD.org> To: freebsd-questions@freebsd.org Subject: Re: Can not add foreign key constraint Message-ID: <ba1f1053-48db-d3c9-f543-087431a87721@FreeBSD.org> In-Reply-To: <CAPORhP4Y7JUn%2BZX6Dtju9KVdqLW11Mv0AddUTL%2BNhQMDOVf5og@mail.gmail.com> References: <CAPORhP4Y7JUn%2BZX6Dtju9KVdqLW11Mv0AddUTL%2BNhQMDOVf5og@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --UTDqon7VjKinDkw376pFqfuIq0QfaI5V7 Content-Type: multipart/mixed; boundary="Mxo3r4a9KoTXg87J8Jgsi02cVuCdHfuXq"; protected-headers="v1" From: Matthew Seaman <matthew@FreeBSD.org> To: freebsd-questions@freebsd.org Message-ID: <ba1f1053-48db-d3c9-f543-087431a87721@FreeBSD.org> Subject: Re: Can not add foreign key constraint References: <CAPORhP4Y7JUn+ZX6Dtju9KVdqLW11Mv0AddUTL+NhQMDOVf5og@mail.gmail.com> In-Reply-To: <CAPORhP4Y7JUn+ZX6Dtju9KVdqLW11Mv0AddUTL+NhQMDOVf5og@mail.gmail.com> --Mxo3r4a9KoTXg87J8Jgsi02cVuCdHfuXq Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 CASC= ADE > ) 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? 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 --Mxo3r4a9KoTXg87J8Jgsi02cVuCdHfuXq-- --UTDqon7VjKinDkw376pFqfuIq0QfaI5V7 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQJ8BAEBCgBmBQJY/anNXxSAAAAAAC4AKGlzc3Vlci1mcHJAbm90YXRpb25zLm9w ZW5wZ3AuZmlmdGhob3JzZW1hbi5uZXQ2NTNBNjhCOTEzQTRFNkNGM0UxRTEzMjZC QjIzQUY1MThFMUE0MDEzAAoJELsjr1GOGkATK0gP/2p1XCABFarU9l1jpKUPhMbL Z8nyJzHhZUPPDCoDBQla/8ZTziJGp4V86/u1uNTtA6qqr+lsJl7DaGLVnadaMBXa 1QGYGKARH/upIcGklGrKrJwHM5ys+LFytDthMxDRrbwEewRFJwkIZVnhDbVDovDq RWi847HYd/vdrrKzghzv62j7/cnI0MnvXB921e1P55YkvZgreebqt1c8LTIogFK3 elwSf71lGbYttbhhP3r5SUwyzwVtBQREXXrjsi4gx717fsyicwyBh36QmXH/YOFP De2bnTA68dL8plgrsaBZnj+A6uGFmfkU3zBaxGCaeZrxvK+IeIGa5who2ajtnT8t nrOM0UOHsIVIqA3c6ZB91YEInW7TtAHawnX9DAL7MosM2tkBByPyb1FpxTp59s4H M1dfqRxh7iLf7PaDF4OSfNJ43m3edQ7Fgci9QhoDZhE5HQh8HWpPjhnjJtE2s0nq mE25vUIWiNKrcVmU6hv8SNb1L4PDjh6BNKjejrAEx9M/4fJqorIDe4R1eIVkfIlB PTKJWbRcJu9ya2qaV60E3lUoQYqaV0evEoGpO49tyYrLFyPduG0rcM/lpT1sK5kX dYjp8m1YIitx13vXziw4ZeAsO5VxwIcCA8ZwF/2yKBHkZa8QgoqqrNWTqHDR8XDd I3+Sh0+bGMRHcUDyw6BC =BLWL -----END PGP SIGNATURE----- --UTDqon7VjKinDkw376pFqfuIq0QfaI5V7--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?ba1f1053-48db-d3c9-f543-087431a87721>