Skip site navigation (1)Skip section navigation (2)
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+ZX6Dtju9KVdqLW11Mv0AddUTL+NhQMDOVf5og@mail.gmail.com>
References:  <CAPORhP4Y7JUn+ZX6Dtju9KVdqLW11Mv0AddUTL+NhQMDOVf5og@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: <http://docs.FreeBSD.org/cgi/mid.cgi?ba1f1053-48db-d3c9-f543-087431a87721>