UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql Workbench (8.0) Foreign Key Problem, MySQL 8.0.16    
 
   
Zaddicus
post Jul 17 2019, 06:57 AM
Post#1



Posts: 137
Joined: 3-April 19
From: Cardiff


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
Attached File  mysqlhell1.PNG ( 52.85K )Number of downloads: 6

(ContactFlags Table) - ContactID = FK
Attached File  mysqlhell2.PNG ( 30.25K )Number of downloads: 5

Relationship window with no options...
Attached File  mysqlhell3.png ( 20.15K )Number of downloads: 6

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
DanielPineault
post Jul 17 2019, 08:01 AM
Post#2


UtterAccess VIP
Posts: 6,770
Joined: 30-June 11



Have you indexed the FK?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
cheekybuddha
post Jul 17 2019, 08:22 AM
Post#3


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Workbench will normally create the index for you if required.

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

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 17 2019, 08:40 AM
Post#4



Posts: 137
Joined: 3-April 19
From: Cardiff


Hi both, thanks for the replies

QUOTE
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)

QUOTE
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
SQL
ALTER TABLE tbl_contactflags
ADD CONSTRAINT FK_ContactID
FOREIGN KEY ( ContactID )
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:
Attached File  mysqlkeyerror.PNG ( 4.95K )Number of downloads: 0


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.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 17 2019, 08:49 AM
Post#5


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


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.

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 17 2019, 09:14 AM
Post#6



Posts: 137
Joined: 3-April 19
From: Cardiff


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.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 19 2019, 05:52 AM
Post#7


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Did you get a solution to this?

If not, can you post the results of running:
CODE
SHOW CREATE TABLE tbl_contacts;
SHOW CREATE TABLE tbl_contactflags;

We can see if we can replicate the issue.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 19 2019, 06:28 AM
Post#8



Posts: 137
Joined: 3-April 19
From: Cardiff


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
CODE
'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'


tbl_contactflags
CODE
CREATE TABLE `tbl_contactflags` (
   `ConFID` bigint(20) NOT NULL AUTO_INCREMENT,
   `FlagID` int(11) DEFAULT NULL,
   `ContactID` bigint(20) DEFAULT NULL,
   PRIMARY KEY (`ConFID`),
   UNIQUE KEY `ConFID_UNIQUE` (`ConFID`),
   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

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 19 2019, 09:02 AM
Post#9


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Hmm...

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

What version of MySQL server are you running, and what version of Workbench
Attached File(s)
Attached File  20190719150233_zaddicus_mysqlwb.png ( 22.96K )Number of downloads: 2
 

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 19 2019, 09:49 AM
Post#10



Posts: 137
Joined: 3-April 19
From: Cardiff


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

CODE
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

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 10:25 PM