From owner-freebsd-questions@freebsd.org Mon Apr 24 09:29:16 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 EE409D4C012 for ; Mon, 24 Apr 2017 09:29:16 +0000 (UTC) (envelope-from carmel_ny@outlook.com) Received: from NAM01-BY2-obe.outbound.protection.outlook.com (mail-oln040092001057.outbound.protection.outlook.com [40.92.1.57]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA384 (256/256 bits)) (Client CN "mail.protection.outlook.com", Issuer "Microsoft IT SSL SHA2" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id B11B7E6C; Mon, 24 Apr 2017 09:29:15 +0000 (UTC) (envelope-from carmel_ny@outlook.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=blEHQDMo3x3TEAs0K4Ojmmn9QNqWzF/NMKLumj0r6fw=; b=Yh6Fbx5ed9KBTdTgu+GjrpFar3mRszUaKGLP82gwwAFMYRahyXY4ojsWHhnk7JjM3r0MrpBtko0R6LXoYiy74bZwzivzlEDr3kMGlNZ0/r/OnY5sWwtBnhO/32eovl8YO4UjQk7pdoOWYJPAThFp4xiQ+n6hKFTSPmhMx0TDPkAIb8+MuFRbhZ8537DU7PHgHqYc94wwWqDOBq3A0zAkFkM77ZOe+5TgprIGtM2XoNIg1iny78tAEOBkzGX4mffSPo0Zd1soCNB5hKps+azW20ajGUTET13/LQRhO7IDII1xDYjcTTpqMM0ABngYzub2vS0aELTpfj4wZ3mzRbzWhQ== Received: from SN1NAM01FT059.eop-nam01.prod.protection.outlook.com (10.152.64.59) by SN1NAM01HT140.eop-nam01.prod.protection.outlook.com (10.152.65.184) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1019.14; Mon, 24 Apr 2017 09:29:14 +0000 Received: from CY1PR20MB0363.namprd20.prod.outlook.com (10.152.64.55) by SN1NAM01FT059.mail.protection.outlook.com (10.152.65.115) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1047.9 via Frontend Transport; Mon, 24 Apr 2017 09:29:15 +0000 Received: from CY1PR20MB0363.namprd20.prod.outlook.com ([10.164.0.29]) by CY1PR20MB0363.namprd20.prod.outlook.com ([10.164.0.29]) with mapi id 15.01.1047.019; Mon, 24 Apr 2017 09:29:14 +0000 From: Gerard Seibert To: Matthew Seaman CC: User questions Subject: Re: Can not add foreign key constraint Thread-Topic: Can not add foreign key constraint Thread-Index: AQHSvN08zFloOEYo/0andggCjl0mZA== Date: Mon, 24 Apr 2017 09:29:14 +0000 Message-ID: References: In-Reply-To: Reply-To: User questions Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: FreeBSD.org; dkim=none (message not signed) header.d=none;FreeBSD.org; dmarc=none action=none header.from=outlook.com; x-incomingtopheadermarker: OriginalChecksum:D40BC643277A47642E8946195BBDEF1BE1325F5E556F46CFEEA0C405261EFDB7; UpperCasedChecksum:FEFE81049142E92E9712EECF2D4270195761C52B7F8A473EE8DF196FB0C4D9BB; SizeAsReceived:8152; Count:44 x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1; SN1NAM01HT140; 5:mvd47ve1y16JzuWfAEWmQPMtFYc7o+eHy8kaBqHSdGdw5rZVK/gadEA3ljojoe6OjYSDBFTFtD9U14enoIdFTAS4JcsXbt/co+O/PKrkH5XzRIUYkb5WSV8eH2ZSeaM3BzGR51ikeZdw7U9RfTJK4QMX/dBwdgIEFE/MmkTHW2E=; 24:mgNJLxtzPH5ljpL2DvtJNtVYhA6s5Nl9V6RdlufOIPTanhNHE0yINTQvH1D9cSpLyI7/+yRWvJhY566ZnhkCnw+Pj32YGbBYG0NM5ivoIZQ=; 7:vWN1WGPKpaFD1RMXGhPSBoUX9oLOmQxeTZ+LRhm18F3mVGw/UOigadgFyADR5GlyqB0WeWVaJac9Z+SOX0PwEJyy7q21ttEvC9kfS4+HoUY8NnkbtdQ4SV6swIs1+7EdK4yzRvpy7w8T8NVDb951IOTFWqfW1uYBIjXulzc+UFYCbwF+blxLOYo2nbGBClVz/4JV9bwxvNGGhhFbSVQlPQcCZiHRI/kdqG3/WGnlFEMVYP3yOugmqCnwBKVkE++40tBvARiZ6TZNyPEFnKodWwj3a9irgO0fmEs+s4yRDVFpCbB6AtZjuVOTCHZDBi/e x-incomingheadercount: 44 x-eopattributedmessage: 0 x-forefront-antispam-report: EFV:NLI; SFV:NSPM; SFS:(7070007)(98901004); DIR:OUT; SFP:1901; SCL:1; SRVR:SN1NAM01HT140; H:CY1PR20MB0363.namprd20.prod.outlook.com; FPR:; SPF:None; LANG:en; x-ms-office365-filtering-correlation-id: 5753c527-116a-4c1d-2c1c-08d48af45ec6 x-microsoft-antispam: UriScan:; BCL:0; PCL:0; RULEID:(22001)(201702061074)(5061506573)(5061507331)(1603103135)(2017031320274)(2017031324274)(2017031323274)(2017031322274)(1603101448)(1601125374)(1701031045); SRVR:SN1NAM01HT140; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(444000031); SRVR:SN1NAM01HT140; BCL:0; PCL:0; RULEID:; SRVR:SN1NAM01HT140; x-forefront-prvs: 0287BBA78D spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: text/plain; charset="us-ascii" Content-ID: Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Apr 2017 09:29:14.5872 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1NAM01HT140 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 09:29:17 -0000 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