MySql/Cloudbees VARCHAR as ForeignKey

Question

I'm having difficulty getting a Foreign Key on a VARCHAR column.

Ideally I would like to have it as a composite FK (as you can see from my attempt at adding it).

Locally, running this script on MySQL 5.6 I get the error # 1215. When running the script on CloudBees I get err 150

I've seen the other questions here on the matter about the data types being exactly the same, I've verified that as you can see from the script.

/* Start of REGION table */

CREATE TABLE REGION (
  ID INTEGER NOT NULL AUTO_INCREMENT,           /* Unique Identifier */
  RORDER INTEGER,                             /* Order to be displayed by (currently unused) */
  LANG VARCHAR(3) DEFAULT 'en' ,                  /* Language of this entry */
  NAME VARCHAR(30) NOT NULL,                    /* Region Name */
  DESCR VARCHAR(50) DEFAULT '' NOT NULL,    /* Region Desc */

  ACTIVE INTEGER DEFAULT '1' NOT NULL,      /* Is item active or not */
  CDATE TIMESTAMP DEFAULT 0,                  /* Create Date */
  MODDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,              /* Last Update Date */
  INSUSER VARCHAR(20) DEFAULT 'SYSTEM' NOT NULL,                                    /* User that inserted this entry */
  MODUSER VARCHAR(20),                            /* User that last modified this entry */

  PRIMARY KEY(id)
) engine InnoDB;

COMMIT;

ALTER TABLE REGION ADD CONSTRAINT UNIQUE (LANG, NAME);

/* End of REGION table */

/* Start of LOCATION table */

CREATE TABLE LOCATION (
  ID INTEGER NOT NULL AUTO_INCREMENT,           /* Unique Identifier */
  REGIONNAME VARCHAR(30) NOT NULL,              /* FK - REGION.ID */
  LORDER INTEGER,                               /* Order to be displayed by (currently unused) */
  NAME VARCHAR(30) DEFAULT '' NOT NULL,     /* Location Name (FK Key) */
  LANG VARCHAR(3) DEFAULT 'en' ,                  /* Language of this entry */
  DESCR VARCHAR(50) DEFAULT '' NOT NULL,    /* Location Desc */

  ACTIVE INTEGER DEFAULT '1' NOT NULL,      /* Is item active or not */
  CDATE TIMESTAMP DEFAULT 0,                  /* Create Date */
  MODDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,              /* Last Update Date */
  INSUSER VARCHAR(20) DEFAULT 'SYSTEM' NOT NULL,                                    /* User that inserted this entry */
  MODUSER VARCHAR(20),                            /* User that last modified this entry */

  PRIMARY KEY(id)
) engine InnoDB;

ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_REGION FOREIGN KEY (REGIONNAME, LANG) REFERENCES REGION(NAME, LANG) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE LOCATION ADD CONSTRAINT UNIQUE (LANG, NAME);

COMMIT;

/* End of LOCATION table */

Answer 1

It required an index on the columns in the REGION table.

Solution:

After REGION is created but before LOCATION is created, add:

CREATE INDEX REG_NAME_LANG ON REGION (NAME, LANG);