<\/p>\n
Having first read all the documentation on Foreign Keys and hence side-stepping the most common pit-falls by making sure all my tables were of type InnoDB<\/i>, all the columns involved had indices and had exactly matching types I was most disappointed to get a bizarre and senseless error from MySQL:<\/p>\n I’m using the latest stable release of MySQL (5.0.22) on OS X 10.4 Tiger.<\/p>\n The first thing I tried to solve this was a bit of googling which led me to loads of threads reminding people about the obvious stuff I was sure to avoid (table type, indices & matching data types). However, deep in the bowls of one of these discussions I did discover that, despite the documentation’s utter failure to mention this fact, foreign keys apparently only work when the columns involved are un-signed numbers.<\/p>\n The columns I was trying to link first were both of type varchar<\/i> so I tried with a different pair of columns of type int<\/i> (which I first set as being un-signed). This worked so I took the identical syntax and tried again with my varchar<\/i>‘s, no joy, just the same uninformative error. It then occurred to me that the items in these columns will always be exactly two characters long so I changed their type to char(2)<\/i> but again I got the same nonsensical error.<\/p>\n Some more googling seems to confirm my theory that MySQL will only be happy with foreign keys that are numeric un-signed types. This is a serious problem for the project I’m now working on. My data has a load of primary keys that are non-numeric that need to referenced as foreign keys all over the place. The two most obvious examples being 2 letter department codes and 3 letter alpha-numeric machine IDs. I could hack this by creating additional integer auto-increment fields in my departments<\/i> and hosts<\/i> tables and using these as primary keys instead of the logical fields but that is wasteful and pointlessly complex.<\/p>\n This is my first foray back in to serious MySQL development for a year having used PostgreSQL in the interim and I think I’ll be switching back to PostgreSQL before you can say ‘MySQL sucks’.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Having first read all the documentation on Foreign Keys and hence side-stepping the most common pit-falls by making sure all my tables were of type InnoDB, all the columns involved had indices and had exactly matching types I was most disappointed to get a bizarre and senseless error from MySQL: Can’t create table ‘.\/epath\/#sql-1a1_f9.frm’ (errno: […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[3,425],"series":[],"class_list":["post-238","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-databases","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7t9xK-3Q","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/posts\/238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/comments?post=238"}],"version-history":[{"count":1,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/posts\/238\/revisions"}],"predecessor-version":[{"id":7409,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/posts\/238\/revisions\/7409"}],"wp:attachment":[{"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/media?parent=238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/categories?post=238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/tags?post=238"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/www.bartbusschots.ie\/s\/wp-json\/wp\/v2\/series?post=238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Can't create table '.\/epath\/#sql-1a1_f9.frm' (errno: 150)<\/pre>\n