mysqlforeign-keysdatabase-cluster

Foreign Keys with MySQL.Cluster (NDB)

Since version 7.3 MySQL Cluster should be capable of foreign key constraints. But here is what happens:

DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
id INT PRIMARY KEY
) ENGINE='InnoDB';

CREATE TABLE t2 (
id INT PRIMARY KEY,
t1id INT
) ENGINE='InnoDB';

ALTER TABLE t2
ADD CONSTRAINT t2t1 FOREIGN KEY (t1id) REFERENCES t1 (id) 
ON DELETE CASCADE ON UPDATE CASCADE;

This is using InnoDB and everything works just fine. Now try it with NDB: 

DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
id INT PRIMARY KEY
) ENGINE='NDB';

CREATE TABLE t2 (
id INT PRIMARY KEY,
t1id INT
) ENGINE='NDB';

ALTER TABLE t2
ADD CONSTRAINT t2t1 FOREIGN KEY (t1id) REFERENCES t1 (id) 
ON DELETE CASCADE ON UPDATE CASCADE;

-- ERROR 150 (HY000): Cannot add foreign key constraint

And now the weird part:

DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
id INT UNIQUE KEY
) ENGINE='NDB';

CREATE TABLE t2 (
id INT PRIMARY KEY,
t1id INT
) ENGINE='NDB';

ALTER TABLE t2
ADD CONSTRAINT t2t1 FOREIGN KEY (t1id) REFERENCES t1 (id) 
ON DELETE CASCADE ON UPDATE CASCADE;

Works just fine.

Is there any rule that says "with the NDB storage engine you cannot reference a primary key column in a foreign key constraint" or "with NDB you have to reference UNIQUE KEYS in foreign key constraints"?

To make things even stranger:

If you replace the definition of t1 by

CREATE TABLE t1 (
id INT UNIQUE KEY NOT NULL
) ENGINE='NDB';

you get the same error.

I'm thinking that PRIMARY KEY implies NOT NULL and that the problem lies not with the former but with the latter.

Solution

  • From what I gathered from MySQL site and Dev blogs:

    An important difference to note with the Foreign Key implementation in InnoDB is that MySQL Cluster does not support the updating of Primary Keys from within the Data Nodes themselves - instead the UPDATE is emulated with a DELETE followed by an INSERT operation. Therefore an UPDATE operation will return an error if the parent reference is using a Primary Key, unless using CASCADE action, in which case the delete operation will result in the corresponding rows in the child table being deleted. The Engineering team plans to change this behavior in a subsequent preview release.

    Support for such operation is not yet confirmed.