Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ PHP, Perl, MySQL and Postgres _ Mysql Workbench (8.0) Foreign Key Problem

Posted by: Zaddicus Jul 17 2019, 06:57 AM

Hi UtterAccess,

This may or may not be something you can help with but I'm having issues creating certain foreign keys for specific tables. A lot of the foreign keys create just fine however there are some tables that I must link together and I just can't do so as I don't even get given the option. When I select the dropdown all it says is "Specify Column..."

I have done some googling before I posted this and the most common clause is inconsistencies in table structure, however I don;t believe this is my issue as:

- both tables use the same chartset/collation.
- both tables are InnoDB,
- both table fields are set to BIGINT(20)
- both table fields are set to NotNull

Note: I know bigint(20) is probably very excessive however this is just more future-proofing and other bigint relationships work perfectly fine (example CompanyID on contacts table)

Anyway here are some screenshots:
(Contacts Table) - ContactID = PK

(ContactFlags Table) - ContactID = FK

Relationship window with no options...

Posted by: DanielPineault Jul 17 2019, 08:01 AM

Have you indexed the FK?

Posted by: cheekybuddha Jul 17 2019, 08:22 AM

Workbench will normally create the index for you if required.

@Castiel - perhaps check ContactID before dropping the Referenced Column list.



Posted by: Zaddicus Jul 17 2019, 08:40 AM

Hi both, thanks for the replies

Have you indexed the FK?

Can confirm workbench creates the indexes when I hit apply changes (If there is the option to select a column in the dropdown) - only thing I do is set the index names to be as short as possible because it hates long names (e.g. this would be 'contacts_cflags' rather than tbl_contacts.ContactID_tbl_contactflags.ContactID)

perhaps check ContactID before dropping the Referenced Column list

What should I check in regards to this?

I did execute a stand-alone statement though the query window in workbench
ALTER TABLE tbl_contactflags
REFERENCES tbl_contacts ( ContactID )

Which then actually applied the FK with no issue HOWEVER, when I then went to test the index by basically deselecting the column in drop down and attempting to reapply through the usual process I got the following error:

Only thing is with this is it doesn't help due to the previously mentioned:
- both tables use the same chartset/collation.
- both tables are InnoDB,
- both table fields are set to BIGINT(20)
- both table fields are set to NotNull

My concern is although I can adapt this statement manually to do all the foreign keys, whether or not they will actually work/apply when attempting to update records or if it will return errors down the line when a user goes to enter data because I'm basically forcing a join that it doesn't like.

Posted by: cheekybuddha Jul 17 2019, 08:49 AM

In your third image in your original post you have not checked 'Column' (ContactID) at the point you drop down the list to view the referenced column options.

I meant check the checkbox to indicate the column to be used as FK. Then perhaps the referenced column list will populate.

Posted by: Zaddicus Jul 17 2019, 09:14 AM

Oh right, it won't let me check the box because there is no option in the drop down - workbench auto-checks that once a valid option has been chosen.

Did a check vs a different table with a BIGINT primary key and it actually does display as a valid choice, it seems to pick and choose what it likes.

Posted by: cheekybuddha Jul 19 2019, 05:52 AM

Did you get a solution to this?

If not, can you post the results of running:

SHOW CREATE TABLE tbl_contacts;
SHOW CREATE TABLE tbl_contactflags;

We can see if we can replicate the issue.


Posted by: Zaddicus Jul 19 2019, 06:28 AM

Hi again,

still having the issue, here are the create table statements - as you can see there is an FK between contacts and contactflags however I basically forced that with the SQL statement in my previous comment and looking at the below I'm not sure if it's even been indexed correctly as a result

'tbl_contacts', 'CREATE TABLE `tbl_contacts` (\n  `ContactID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `Email` varchar(255) NOT NULL,\n  `FirstName` varchar(255) NOT NULL,\n  `MiddleNames` varchar(255) DEFAULT NULL,\n  `LastName` varchar(255) NOT NULL,\n  `Position` varchar(255) NOT NULL,\n  `CompanyID` bigint(20) NOT NULL,\n  `LocationID` int(11) NOT NULL COMMENT \'Contact location\',\n  `OfficeNumber` varchar(255) DEFAULT NULL,\n  `MobileNumber` varchar(255) DEFAULT NULL,\n  `TwitterHandle` varchar(255) DEFAULT NULL,\n  `LinkedIn` varchar(255) DEFAULT NULL,\n  `PAName` varchar(255) DEFAULT NULL,\n  `PAEmail` varchar(255) DEFAULT NULL,\n  `PAPhone` varchar(255) DEFAULT NULL,\n  `Source` varchar(255) DEFAULT NULL,\n  `Suppress` tinyint(1) DEFAULT NULL,\n  `TempSupp` tinyint(1) DEFAULT NULL,\n  `FailedVerification` varchar(255) DEFAULT NULL,\n  `Notes` varchar(255) DEFAULT NULL,\n  `ImportDate` datetime DEFAULT NULL,\n  PRIMARY KEY (`ContactID`),\n  UNIQUE KEY `Email_UNIQUE` (`Email`),\n  UNIQUE KEY `ContactID_UNIQUE` (`ContactID`),\n  KEY `contacts_companies_idx` (`CompanyID`) /*!80000 INVISIBLE */,\n  KEY `contacts_location_idx` (`LocationID`),\n  CONSTRAINT `contacts_companies` FOREIGN KEY (`CompanyID`) REFERENCES `tbl_lst_companies` (`CompanyID`),\n  CONSTRAINT `contacts_location` FOREIGN KEY (`LocationID`) REFERENCES `tbl_lst_location` (`LocationID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

CREATE TABLE `tbl_contactflags` (
   `FlagID` int(11) DEFAULT NULL,
   `ContactID` bigint(20) DEFAULT NULL,
   KEY `contactflags_flags_idx` (`FlagID`),
   KEY `contactflags_contacts` (`ContactID`),
   CONSTRAINT `contactflags_contacts` FOREIGN KEY (`ContactID`) REFERENCES `tbl_contacts` (`ContactID`),
   CONSTRAINT `contactflags_flags` FOREIGN KEY (`FlagID`) REFERENCES `tbl_lst_flags` (`FlagID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Posted by: cheekybuddha Jul 19 2019, 09:02 AM


I can't reproduce - it works OK for me.

What version of MySQL server are you running, and what version of Workbench


Posted by: Zaddicus Jul 19 2019, 09:49 AM

It is a very odd error becuase it only seems to impact certain tables/foreign keys.

I'm using Workbench Version 8.0.16 build 14498383 CE
Server version: 8.0.16 MySQL Community Server - GPL

mysql> SHOW VARIABLES LIKE "%version%";
| Variable_name            | Value                        |
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.16                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| slave_type_conversions   |                              |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2        |
| version                  | 8.0.16                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Win64                        |
| version_compile_zlib     | 1.2.11                       |

Only thing I could think of is it's configured to MySQL Server 5.5 legacy authentication due to visual studio 2019 being released but no updates on the connectors so it won't connect to workbench with the new authentication method until they get an update - but then that's only the authentication method and shouldn't have any impact on the indexes/foreign keys