InnoDB: Changing the length of a VARCHAR primary Key

I have a table in InnoDB which uses a VARCHAR based ForeignKey:

CREATE TABLE `portal_equity` (
   `isin` varchar(12) NOT NULL,
    ....,
    PRIMARY KEY (`isin`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Another table has a ForeignKey constraint to that table:

CONSTRAINT `equity_id_refs_isin_2581bb1d` FOREIGN KEY (`equity_id`) REFERENCES `portal_equity` (`isin`)

I needed to change the length of the primary key VARCHAR column, so I did. Now I keep getting the following integrity error every time I want to reference a row from the "portal_equity" table where the isin exceeds the original length of 12.

Foreign key constraint fails for table investtor.portal_equitypastvalue: CONSTRAINT equity_id_refs_isin_7eed44e7 FOREIGN KEY (equity_id) REFERENCES portal_equity (isin) Trying to add in child table, in index portal_equitypastvalue_equity_id_6e7526e1 tuple: DATA TUPLE: 3 fields; 0: len 12; hex 304c55303332333133343030; asc 0LU032313400;; 1: len 3; hex 8fb578; asc x;; 2: len 4; hex 8001398a; asc 9 ;;

But in parent table investtor.portal_equity, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 13; hex 304c5530333233313334303036; asc 0LU0323134006;; 1: len 6; hex 000000005139; asc Q9;; 2: len 7; hex 800000002d0110; asc - ;; 3: len 4; hex 61736466; asc asdf;; 4: len 4; hex 80000050; asc P;; 5: len 3; hex 736466; asc sdf;; 6: len 4; hex 80000001; asc ;; 7: len 2; hex 4144; asc AD;; 8: len 0; hex ; asc ;;

Do I need to regenerate all foreignkey constraints? That seems rather excessive. Is this be开发者_运维问答havior/error documented somewhere?

---

Do I need to regenerate all foreignkey constraints?

Yes.

That seems rather excessive.

If you change the datatype of a primary key field, it seems natural to me that you the datatype of the foreign key fields (and their respective indexes and constraints) also need to be changed.

Although the documentation states 'The length of string types need not be the same', logical sense dictates that the foreign key field should at least be as long as any data you want to store in it.

Good luck!