MySQL Cluster database & MySQL Replication
RSS icon Email icon Home icon

  • Foreign Keys in MySQL Cluster

    Posted on June 18th, 2013 andrew 19 comments
    Foreign Key constraints between tables

    Foreign Key constraints between tables

    The newly announced GA of MySQL Cluster 7.3 (7.3.2) builds upon second DMR (7.3.1 m2) released earlier in the year which added Foreign Keys to MySQL Cluster. Foreign Keys is a feature requested by many, many people and has often been cited as the reason for not being able to replace InnoDB with MySQL Cluster when they needed the extra availability or scalability.

    Note that this post is an up-version of the original – and was first published with the 7.3 labs release in June 2012.

    What’s a Foreign Key

    The majority of readers who are already familiar with Foreign Keys can skip to the next section.

    Foreign Keys are a way of implementing relationships/constraints between columns in different tables. For example, in the above figure, we want to make sure that the value of the county column in the towns table has an associated entry in the counties table. In this way, no-one can place a town in a non-existent county and similarly no one can remove a county and leave orphaned towns behind.

    We refer to the towns table as the child and the counties table as the parent.

    There are different categories of constraints that influence how they’re enforced when a row is updated or deleted from the parent table:

    • Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.
    • Restrict: A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.
    • No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.
    • Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.
    • Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.

    How to use Foreign Keys with MySQL Cluster

    For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.

    First of all, create the tables and add some data:

    mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
  ENGINE=ndb;
mysql> INSERT INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
  ('Oxfordshire','England'),('Buckinghamshire','England');
mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
  INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county) 
  REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
  ('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | England |
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

    Note that the FK is actually defined as part of creating the towns table with CONSTRAINT county_town FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT. county_town is the label assigned to the constraint (if you don’t specify one then one will be automatically created – you can see it by executing SHOW CREATE TABLE towns). The constraint is set up from the county column in the towns (child) table to the county column in the counties (parent) table.

    As a first check, we can confirm that values that are not part of the constraint can be modified in the parent table – in this case we’ll move the England/Wales border:

    mysql> UPDATE counties SET country='WALES' WHERE county='Shropshire';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

    Next, to illustrate the kind of user error that can be prevented, we attempt to add a town in a county that has not yet been defined – we want this to fail as it would break the constraint we created:

    mysql> INSERT towns VALUES ('Cambridge','Cambridgeshire');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  (Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                               |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 255 'Foreign key constaint violated: No parent row found' from NDB          |
| Error   | 1452 | Cannot add or update a child row: a foreign key constraint fails (Unknown error code) |
+---------+------+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

    We can also confirm that the constraint checking works in the opposite direction, by attempting to delete a county that has towns defined for it:

    mysql> DELETE FROM counties WHERE county='Berkshire';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 256 'Foreign key constaint violated: Referenced row exists' from NDB            |
| Error   | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (Unknown error code) |
+---------+------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

    It is also possible to delete FK constraints and add new ones, we do that next and at the same time we’ll switch to a CASCADE restriction. Note that adding and deleting FKs are on-line operations. In case you have forgotten the name of the constraint (or we had not explicitly named it) we can use the SHOW CREATE TABLE command to remind us:

    mysql> SHOW CREATE TABLE towns;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| towns | CREATE TABLE `towns` (
  `town` varchar(30) NOT NULL,
  `county` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`town`),
  KEY `county_county_index` (`county`),
CONSTRAINT `county_town` FOREIGN KEY(`county`) REFERENCES `counties` (`county`)
  ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE towns DROP FOREIGN KEY county_town;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE towns ADD CONSTRAINT county_town FOREIGN KEY (county)
  REFERENCES counties(county) ON DELETE CASCADE ON UPDATE CASCADE;

    The data in the tables will not have changed as part of dropping and adding constraints and so you can always DROP and the re-add constraints if you need to turn FKs off for a while:

    mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
| Shropshire      | WALES   |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

    Now if we delete the county of Berkshire then the CASCADE option will cause all of the associated towns to be deleted too:

    mysql> DELETE FROM counties WHERE county='Berkshire';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Oxfordshire     | England |
+-----------------+---------+
3 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Shrewsbury | Shropshire  |
| Oxford     | Oxfordshire |
+------------+-------------+
2 rows in set (0.00 sec)

    Note that MySQL Cluster implements FKs in the data nodes and the constraints will be enforced regardless of how the data is written (through SQL, javascript, Memcached, ClusterJ, REST or directly through the NDB API). Before illustrating this, we add back the data we have just deleted:

    mysql> INSERT INTO counties VALUES ('Berkshire','England');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Berkshire       | England |
| Oxfordshire     | England |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Reading    | Berkshire   |
| Shrewsbury | Shropshire  |
| Maidenhead | Berkshire   |
| Oxford     | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)

    We’ll then run a simple C++ app to delete the ‘Berkshire’ row from the counties table. The full source for the app is available here but the key part of the code is this:

    static void do_change(Ndb &myNdb)
{
const NdbDictionary::Dictionary* myDict= myNdb.getDictionary();
const NdbDictionary::Table *countiesTable= myDict->getTable("counties");

char royalBorough[255];
royalBorough[0]=9;
strcpy(royalBorough + 1, "Berkshire");

if (countiesTable == NULL)
APIERROR(myDict->getNdbError());

NdbTransaction *myTransaction= myNdb.startTransaction();
if (myTransaction == NULL) APIERROR(myNdb.getNdbError());

NdbOperation *myOperation= myTransaction->getNdbOperation(countiesTable);
if (myOperation == NULL) APIERROR(myTransaction->getNdbError());

std::cout << "About to delete " << royalBorough << "\n";

myOperation->deleteTuple(); 
    myOperation->equal("county", (const char *) royalBorough);

if (myTransaction->execute( NdbTransaction::Commit ) == -1)
APIERROR(myTransaction->getNdbError());

myNdb.closeTransaction(myTransaction);
}

    We then run this code and confirm that the cascaded delete has worked:

    billy@ws2:~/projects/fk$ ./testfk localhost:1186
About to delete         Berkshire

mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county          | country |
+-----------------+---------+
| Shropshire      | WALES   |
| Buckinghamshire | England |
| Oxfordshire     | England |
+-----------------+---------+
3 rows in set (0.00 sec)

+------------+-------------+
| town       | county      |
+------------+-------------+
| Shrewsbury | Shropshire  |
| Oxford     | Oxfordshire |
+------------+-------------+
2 rows in set (0.00 sec)

    Comparison with InnoDB’s Foreign Keys

    As mentioned, we’ve attempted to mirror the way that InnoDB works as much as possible in the way that it implements Foreign Keys as that’s what we think will allow people to move more easily to MySQL Cluster when appropriate. There are a couple of exceptions though:

    • InnoDB doesn’t support “No Action” constraints, MySQL Cluster does
    • You cannot set up FKs between 2 tables where one is stored using MySQL Cluster and the other InnoDB.
    • You cannot change primary keys through the NDB API which means that the MySQL Server actually has to simulate such operations by deleting and re-adding the row. If the PK in the parent table has a FK constraint on it then this causes non-ideal behaviour. With Restrict or No Action constraints, the change will result in an error. With Cascaded constraints, you’d want the rows in the child table to be updated with the new FK value but, the implicit delete of the row from the parent table would remove the associated rows from the child table and the subsequent implicit insert into the parent wouldn’t reinstate the child rows. For this reason, an attempt to add an ON UPDATE CASCADE where the parent column is a primary key will be rejected.

    MySQL Workbench

    Viewing & updating Foreign Key Constraints in MySQL Workbench

    Viewing and updating Foreign Key Constraints in MySQL Workbench

    While not really the subject of this post, MySQL Workbench is a great tool for database developers and one of the features is the creation, modification and reverse engineering of schemas. For example, you could import your existing schema, add a new table and then introduce a FK constraint between an existing table and your new one. Previously, WorkBench would only allow you to play with FKs for InnoDB tables.

    Closing remarks

    We believe that this is going to enable a whole new set of applications exploit the advantages of MySQL Cluster (on-line scalability, real-time performance, NoSQL APIs, real-time performance, on-line schema changes,…) either because:

    • they want to simplify their application by pushing these referential checks down into the database – letting the developer focus more of their time on extending the application;
    • because they’re stuck with 3rd party middleware that is dependent on FKs
    • or that the application is already so dependent on FKs that it would be too much pain to remove them.

    If you are new to MySQL Cluster then here are some useful resources to get you started:

    MySQL Cluster , ,
     

    17 responses to “Foreign Keys in MySQL Cluster” RSS icon

    • Dhanush Gopinath July 3rd, 2012 at 07:49

      Hi,

      We are evaluating mysql cluster and started with 7.2 version and moved onto 7.3 test version. 7.3 looks quite stable, and the foreigh keys are created and indexed. At times when memory limit is approached on all data nodes, the all data nodes in the cluster gets forcefully shutdown. Is this the way it behaves? I will try to post an error message next time it happens.

      Also we didn’t notice any significant improvement w.r.t querying. The performance improvement of the same query in 7.2 and 7.3 is negligible. How much of a performance improvement are you expecting. Or is there any more configurations which we have to do?

      Dhanush Gopinath

    • Dhanush,

      MySQL Cluster 7.2 introduced major performance improvements for joins and when using the multi-threaded data node on servers with lots of cores.

      What the 7.3 DMR focussed on was showcasing Foreign Key support rather than additional performance gains.

      Please do post the errors/logs if you see your cluster shut down.

      Regards, Andrew.

    • Dhanush Gopinath July 11th, 2012 at 07:06

      We figured out most of the errors was due to the lack of enough RAM in our systems. When we have increased them, we don’t see it anymore. Previously using an 8GB RAM, we have configured 5.8GB for Data memory and 500 M for Index Memory. Whenever data used to touch more than 20 million rows, one of them will shut down.

      all report memory showed around 95% of usage. What is the best amount of usage in this case? Should we try adding more memory when it touches, say around 70%? Would the querying be slower if memory usage is more?

    • If you’re using in-memory tables then performance shouldn’t be impacted by running low on RAM as all of the data is in memory anyway (though you may want to set lockpagesinmainmemory to prevent things getting swapped to disk). Having said that, it’s always good to have a bit of spare headroom!

    • Dhanush Gopinath July 12th, 2012 at 11:56

      Just when I said all is well after increasing the RAM, I got the error again. We had 2 data nodes and I was doinng a rolling restart of a node after reducing the Datamemory from 12.5 G to 6 G, I came across this error:

      We are facing this issue most of the times when we restart a node after adding or reducing DataMemory

      Time: Thursday 12 July 2012 – 16:56:09
      Status: Temporary error, restart node
      Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug)
      Error: 2341
      Error data: DbtcMain.cpp
      Error object: DBTC (Line: 16955) 0×00000002
      Program: ndbd
      Pid: 6613
      Version: mysql-5.5.22 ndb-7.3.0
      Trace: /rcloud/rcloud_cluster_73/ndb_data/ndb_4_trace.log.2 [t1..t1]
      ***EOM***

      I see that this is already a bug http://bugs.mysql.com/bug.php?id=64609

      Is this an issue in 7.3 ?

    • Dhanush,

      to get some more eyes looking at this, I’d suggest that you also post the full issue description on http://forums.mysql.com/list.php?25

      Regards, Andrew.

    • Andrew,

      The new MySQL Cluster 7.3 test version looks very promising. However after inserting a few thousand rows and then trying to delete part of the data it was causing data nodes to terminate.

      There was also a strange output from “SHOW CREATE TABLE” if an FK constraint is applied against another table, it shows against the current table. SHOW CREATE TABLE config …. CONSTRAINT `configid` FOREIGN KEY `configid` references config (configid) on delete no action on update no action. Referencing itself.

      Would it be possible to get a newer release of 7.3? As it’s been around three months and I’m super excited to use the new FK features which 7.3 will offer. MySQL Cluster 7.3 would be a perfect fit, so I’m very keen.

      Thanks, John

    • Hi John,

      Great to hear that you’re trying this stuff out!

      A couple of comments – the first is that yes, you can expect further versions of MySQL Cluster 7.3. The second would be that if you think you’ve found a bug then we’d really appreciate it if you could create a bug at http://bugs.mysql.com/ and provide as much information as possible on how we can recreate the issue.

      Thanks, Andrew.

    • Hi guys!
      I have installed MySQL Cluster ver 7.3. for have foreign key support but I have two problems:

      1) I can’t create foreign key using ON UPDATE CASCADE option. I have this error: #150 – Cannot add foreign key constraint
      My SQL comand is like this:

      ALTER TABLE `xxxxxxx`
      ADD CONSTRAINT `xxxxxxxx_ibfk_1` FOREIGN KEY (`groupname`) REFERENCES `HsProductsPolicy` (`Code`) ON DELETE CASCADE ON UPDATE CASCADE;

      If I delete ON UPDATE CASCADE all is OK: why?

      2) I can’t create foreign key from two tables using an index that isn’t PRIMARY KEY: is it possibile?
      I have, also in this case, this error:#150 – Cannot add foreign key constraint

      I hope that someone help me

      Best regards

    • Mirio,

      You don’t show the full schema but I imagine that you’re hitting the main difference between FKs with MySQL Cluster vs. InnoDB – namely that the FK constraint isn’t allowed to update the value of a Primary Key. Is this the case?

      Andrew.

    • Dear Andrew!
      Thanks for your answer.
      For the first case I think problem is that FK constraint is not allowed to update Primary Key value but What about my second case?
      Here my table schema and FK that generate error: #150 – Cannot add foreign key constraint

      CREATE TABLE IF NOT EXISTS `HsProductsPolicy` (
      `HsProductsPolicyID` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID della policy per i prodotti’,
      `Code` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘Codice della policy. Corrisponderà al nome del gruppo di FreeRadius’,
      `Notes` text COLLATE utf8_unicode_ci COMMENT ‘Note della policy’,
      `ForFreeHotSpots` tinyint(1) NOT NULL DEFAULT ’0′ COMMENT ‘Se True la policy è per prodotti da associare a HotSpots liberi’,
      `TrafficBandwidthModulation` int(11) NOT NULL DEFAULT ’0′ COMMENT ‘Abilita la modulazione della velocità in base al traffico. 0 = Non abilitato; 1 = Abilitato medio; ecc.’,
      `TrafficBandwidthModulationFromTime` int(11) NOT NULL DEFAULT ’0′ COMMENT ‘Attiva dalle ore’,
      `TrafficBandwidthModulationToTime` int(11) NOT NULL DEFAULT ’0′ COMMENT ‘Attiva fino alle ore’,
      `TrafficBandwidthModulationHours` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Periodo di tempo espresso in ore da considerare per l”analisi del traffico effettuato per la modulazione della velocità’,
      `TrafficBandwidthModulationTrafficUp` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Traffico limite in upload per la modulazione della velocità’,
      `TrafficBandwidthModulationTrafficDown` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Traffico limite in download per la modulazione della velocità’,
      `BandwidthMinUp` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Velocità minima di upload’,
      `BandwidthMinDown` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Velocità minima di download’,
      `BandwidthMaxDown` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Velocità massima di download’,
      `BandwidthMaxUp` int(20) NOT NULL DEFAULT ’0′ COMMENT ‘Velocità massima di upload’,
      PRIMARY KEY (`HsProductsPolicyID`),
      KEY `HsProductsPolicyCode` (`Code`)
      ) ENGINE=NDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED COMMENT=’Tabella con l”elenco delle policy’ AUTO_INCREMENT=8 ;

      CREATE TABLE IF NOT EXISTS `radgroupreply` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
      `attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
      `op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘=’,
      `value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT ”,
      PRIMARY KEY (`id`),
      KEY `GroupName_IDX` (`groupname`)
      ) ENGINE=NDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=129 ;

      ALTER TABLE `radgroupreply`
      ADD CONSTRAINT `radgroupreply_ibfk_1` FOREIGN KEY (`groupname`) REFERENCES `HsProductsPolicy` (`Code`) ON DELETE CASCADE ;

      Thanks in advance

      Best regards

    • Any news????

      Best regards

    • Thanks for the detailed blog post, it’s great to hear that MySQL Cluster will support foreign keys soon.

      I’ve been playing with 7.3 and I notice that it doesn’t support foreign keys in conjuction with a custom partitioning scheme – for example:

      CREATE TABLE Parent (
      id int not null PRIMARY KEY
      )
      ENGINE=NDB
      ;

      – NB: I’ve used a “PARTITION BY KEY” directive
      – to ensure the child rows are on the same node
      – as their parent row.
      CREATE TABLE Child (
      id int not null,
      parentId int not null,
      INDEX idxChild_parentId (parentId),
      UNIQUE KEY (parentId, id),
      CONSTRAINT fkChild_parentId
      FOREIGN KEY (parentId) REFERENCES Parent (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT
      )
      ENGINE=NDB
      PARTITION BY KEY (eventId)
      ;

      This gives “ERROR 1506 (HY000) at line 7: Foreign key clause is not yet supported in conjunction with partitioning”

      I wonder if the production release of 7.3 will support this? Or is what I’m trying to do achievable another way (or a bad idea, for that matter)?

    • Hi Kieran,

      thanks for pointing this out – I’ve just recreated it. This appears to be a constraint that’s been inherited from InnoDB (we aim to match InnoDB behaviour but what your attempting to do makes a lot of sense for Cluster tables). I’m looking into why the restriction is needed (suspecting that it shouldn’t be needed for Cluster) and whether we can remove it for NDB tables.

      Andrew.

    • Hi Kieran,

      just tested this scenario on the latest internal version of Cluster 7.3 (a test that failed in the same way as yours in the DMR)…

      mysql> CREATE TABLE parent (id INT NOT NULL PRIMARY KEY) ENGINE=NDB;
      Query OK, 0 rows affected (0.78 sec)

      mysql> CREATE TABLE child (id INT NOT NULL,parentId INT NOT NULL,INDEX idxChild_parentId (parentId), PRIMARY KEY(parentId, id), CONSTRAINT fkChild_parentId FOREIGN KEY (parentId) REFERENCES parent (id) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=NDB PARTITION BY KEY(parentId);
      Query OK, 0 rows affected (1.01 sec)

      … so I can confirm that Fks and paritions will be compatible when we release the GA version.

      Thanks again for the feedback.

      Andrew.

    • Has anyone run into problems with crossing NDB and INNODB tables Parent/Child FK issues in 7.3.1 m2? Looks like FK is only valid between NDB to NDB tables and cannot be mixed ? Can someone verify this? I assume this is not supported ? I get

      create table wtestinnodb(col1 int, col2 varchar(20), constraint `f1` foreign key(col1) references wtestndb(col1))engine=innodb;
      ERROR 1215 (HY000): Cannot add foreign key constraint

      where wtestndb is ndb table.

    • Unfortunately, you can have FK that span NDB and InnoDB tables as the constraints are implemented down in the storage engines.

      Andrew.


    2 Trackbacks / Pingbacks

    • [...] Read this blog for a demonstration of using Foreign Keys with MySQL Cluster.  [...]

    • [...] MySQL Cluster and NoSQL I mentioned the new DMR 7.3.1 for MySQL Cluster. Our efforts to improve ease of use for Cluster continues, please learn more aboutm, and try out the autoinstaller. MySQL Cluster was there as a “NoSQL” database long before the term was coined, and was just awarded the “storage engine of the year“. This DMR also contains support for Foreign Keys which I know some of you have been waiting for. I’ll encourage the cluster engineers to write an in depth blog about the complexities in supporting foreign keys in a distributed databases. Meanwhile please learn more here. [...]

    Leave a reply

Search

Maidenhead self catering accommodation
This site is independent of Oracle/MySQL