Description:
Unlike InnoDB, NDB insists that the parent index is unique and the addition of a constraint fails if it isn't. As this difference exists, it should be documented.
How to repeat:
CREATE TABLE campuses (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
code varchar(30) NOT NULL,
PRIMARY KEY (id),
KEY name (name),
KEY code (code)
) ENGINE=ndbcluster AUTO_INCREMENT=152 DEFAULT CHARSET=latin1;
CREATE TABLE thresholdtriggers (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(60) NOT NULL,
threshold_amount decimal(10,2) NOT NULL,
emails text NOT NULL,
PRIMARY KEY (id)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS thresholdtriggerstocampuses (
id int(11) NOT NULL AUTO_INCREMENT,
threshold_trigger_id int(11) NOT NULL,
campus_code varchar(30) NOT NULL,
PRIMARY KEY (id),
KEY threshold_trigger_id (threshold_trigger_id),
KEY campus_code (campus_code),
CONSTRAINT thresholdtriggerstocampuses_ibfk_2 FOREIGN KEY (campus_code) REFERENCES campuses (code) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 21026 'Create foreign key failed in NDB - parent index is not unique index' from NDB |
| Error | 1215 | Cannot add foreign key constraint |
+---------+------+------------------------------------------------------------------------------------------------+
If I then make campuses.code be a UNIQUE key then it passes.
Suggested fix:
Document this restriction in http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
