MySQL Big FK identifier

Question

I'm trying to create a fk but MySQL doesn't allow it ...

Identifier name 'foobarbaz_FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_PRODUCT_ENTITY' is too long [ CREATE TABLE `foobarbaz_weee_discount` (   `entity_id` int(10) unsigned NOT NULL DEFAULT '0',   `website_id` smallint(5) unsigned NOT NULL DEFAULT '0',   `customer_group_id` smallint(5) unsigned NOT NULL,   `value` decimal(12,4) NOT NULL DEFAULT '0.0000',   KEY `foobarbaz_FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_WEBSITE` (`website_id`),   KEY `foobarbaz_FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_PRODUCT_ENTITY` (`entity_id`),   KEY `foobarbaz_FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_GROUP` (`customer_group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ]

Is there a way to add big fks? Thank you.

Answer 1

The foreign key is not the problem. It's the name of the key. I agree with MySQL, 'foobarbaz_FK_CATALOG_PRODUCT_ENTITY_WEEE_DISCOUNT_PRODUCT_ENTITY' is a bit silly :)

You have "PRODUCT_ENTITY" twice in the name. Start by dropping that :)

Answer 2

I doubt it. You can try to change the datatype of the system table for foreign keys. However I never tried to change system tables before so no idea if there will be any complications if it is even possible.

Answer 3

It looks like this may be a bug. Here is the report that I found. I didn't see that it was patched yet but you can just rename your keys so that then length is shorter.

http://bugs.mysql.com/bug.php?id=13942

Answer 4

No, it's hard coded into MySQL, see the docs (towards the bottom of the page). But it has nothing to do with the name of the FK. It's the name of the index that's too long (64 characters max):

`Identifier`                  `Maximum Length (characters)`
Database                      64
Table                         64
Column                        64
Index                         64
Constraint                    64
Stored Procedure or Function  64
Trigger                       64
View                          64
Alias                         256 (see exception following table)
Compound Statement Label      16