From owner-freebsd-questions@freebsd.org Mon Apr 24 07:31:32 2017 Return-Path: Delivered-To: freebsd-questions@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id 76B0ED4DDAC for ; Mon, 24 Apr 2017 07:31:32 +0000 (UTC) (envelope-from matthew@FreeBSD.org) Received: from smtp.infracaninophile.co.uk (smtp.infracaninophile.co.uk [81.2.117.100]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "smtp.infracaninophile.co.uk", Issuer "infracaninophile.co.uk" (not verified)) by mx1.freebsd.org (Postfix) with ESMTPS id 05A4EFDA for ; Mon, 24 Apr 2017 07:31:31 +0000 (UTC) (envelope-from matthew@FreeBSD.org) Received: from liminal.local (unknown [IPv6:2001:8b0:151:1:1c1d:86a1:a200:b700]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: m.seaman@infracaninophile.co.uk) by smtp.infracaninophile.co.uk (Postfix) with ESMTPSA id D71ADC118 for ; Mon, 24 Apr 2017 07:31:25 +0000 (UTC) Authentication-Results: smtp.infracaninophile.co.uk; dmarc=none header.from=FreeBSD.org Authentication-Results: smtp.infracaninophile.co.uk/D71ADC118; dkim=none; dkim-atps=neutral Subject: Re: Can not add foreign key constraint To: freebsd-questions@freebsd.org References: From: Matthew Seaman Message-ID: Date: Mon, 24 Apr 2017 08:31:19 +0100 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0) Gecko/20100101 Thunderbird/45.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="UTDqon7VjKinDkw376pFqfuIq0QfaI5V7" X-Spam-Status: No, score=-0.4 required=5.0 tests=BAYES_00,RDNS_NONE, SPF_SOFTFAIL autolearn=no autolearn_force=no version=3.4.1 X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on smtp.infracaninophile.co.uk X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 Apr 2017 07:31:32 -0000 This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --UTDqon7VjKinDkw376pFqfuIq0QfaI5V7 Content-Type: multipart/mixed; boundary="Mxo3r4a9KoTXg87J8Jgsi02cVuCdHfuXq"; protected-headers="v1" From: Matthew Seaman To: freebsd-questions@freebsd.org Message-ID: Subject: Re: Can not add foreign key constraint References: In-Reply-To: --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--