目前MySQL 集群存储引擎(ndbcluster)ndb-7.2.4版本不支持外键。
如果想在MySQL 集群中使用外键可通过触发器来实现。
MySql Cluster Version:
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @127.0.0.1 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0, Master)
id=4 @127.0.0.1 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @127.0.0.1 (mysql-5.5.19 ndb-7.2.4)
[mysqld(API)] 2 node(s)
id=50 @127.0.0.1 (mysql-5.5.19 ndb-7.2.4)
id=51 (not connected, accepting connect from any host)
a)创建一个存储错误信息的表
CREATE TABLE error_msg
(error_msg VARCHAR(32) NOT NULL PRIMARY KEY)ENGINE=NDB;
INSERT INTO error_msg VALUES (‘Foreign Key Constraint Violated!’);
b) 创建主表
CREATE TABLE ndb_parent
(
nparent_id INT NOT NULL,
PRIMARY KEY (nparent_id)
) ENGINE=NDB;
c)创建子表
CREATE TABLE ndb_child
(
nparent_id INT NOT NULL,
nchild_id INT NOT NULL,
PRIMARY KEY (nparent_id, nchild_id)
) ENGINE = NDB;
d)创建触发器
CREATE TRIGGER insert_ndb_child
BEFORE INSERT
ON ndb_child
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM ndb_parent WHERE nparent_id=new.nparent_id)= 0
THEN
INSERT error_msg VALUES ('Foreign Key Constraint Violated!');
END IF;
END;
e) 测试先在主表插入记录,后在子表插入记录。可以插入成功。
INSERT INTO ndb_parent VALUES (1), (2), (3);
INSERT INTO ndb_child VALUES (1,1), (1,2), (2,1), (2,2), (2,3), (3,1);
如果子表中插入的记录在主表中不存在会出现错误。
INSERT INTO ndb_child VALUES (4,1);
[SQL] INSERT INTO ndb_child VALUES (4,1);
[Err] 1062 - Duplicate entry 'Foreign Key Constraint Violated!' for key 'PRIMARY'