Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

foreign key support for NDBCLUSTER

Posted on 2008-10-22
5
Medium Priority
?
1,035 Views
Last Modified: 2008-11-19
Hi,
   i read that innoDB engine supports foreign key,but not clustering.
and NDBclustering engine will support clustering but not foerign key.
however,
i wanted to use both clustering and foreign key support in my application.
please tell.is there any other way to do it.
Regds,
vijaya
0
Comment
Question by:vijji_lakshmi
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22789775
The NDB engine does not explicitly support foreign keys, but it does not disallow the functionality if you choose to implement it yourself.  If your database-layer API does the work in verifying adherence to a foreign key schema, you can effectively duplicate that functionality.  For example, instead of relying on MySQL to return an error if a DELETE would result in orphaned child rows, your API should do the check with an additional query.

It is a bit more work, and it will slow down your application a little, but it can be done.
0
 

Author Comment

by:vijji_lakshmi
ID: 22793682
can you please provide with the examples link.where can i refer how they done.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22796560
Your absolute best option is to gain a firm understanding of how foreign keys work.  Once you grasp the concepts, duplicating their actions through your own queries is relatively simple.  I've given some hints and tips below, but be sure to read through the foreign key sections in the MySQL manual:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

First, you have to decide your FK strategy.  Will you cascade changes, or restrict them, etc.?  There are three aspects you need to consider.  All of the examples below consider two tables: table1 (parent) and table2 (child)

For delete queries, you will need to check for children.  If you are cascading, then your queries are relatively simple:

DELETE FROM table1 WHERE id=1
DELETE FROM table2 WHERE FK_id=1

Ideally, these queries would be in a transaction so that they both succeed or both fail.  If only one succeeds, you'll be left with an orphan somewhere.  If you are restricting, then you have to check for a child first.  If a child exists, cancel the delete query:

<?
$query = "SELECT * FROM table2 WHERE FK_id=1";
$result=mysql_query($query);
$check_value=mysql_num_rows($result);
if ((int)$check_value > 0) {
  // children exist...do not delete
} else {
  // no children exist
  $query = "DELETE FROM table1 WHERE id=1";
}
?>

For INSERT queries, you'll need to check for the existence of a parent.  Normal FKs do not allow for inserting a child with a valid parent.  Since you're doing this manually, you theoretically *could* allow it, but you'd be left with an orphan.  The check might look something like this:

<?
$query = "SELECT * FROM table1 WHERE id=1";
$result=mysql_query($query);
$check_value=mysql_num_rows($result);
if ((int)$check_value > 0) {
  // parent exists
  $query = "INSERT INTO table2 (field1,field2) VALUES ('value1','value2')";
  $result=mysql_query($query);
} else {
  // no parent exists
}
?>

For update queries, it gets a little more complex, but it's the same basic idea.  For every change you need to push down to a child, create a query to do that job.  Again, all the foreign key queries you create would (ideally) be part of the same transaction to prevent your constraints from breaking.
0
 

Author Comment

by:vijji_lakshmi
ID: 22865466
i read that there is an alreadu existing NDB API for supporting foreign key.
can you please let me know how to use it with java??
please reply me soon it is very urgent.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1500 total points
ID: 22872243
There is an existing API for NDB.  You can find it here:

http://dev.mysql.com/doc/ndbapi/en/index.html

But it does seem to have support for foreign keys any more than the docs say it is supposed to.  Have you seen another API capable of supporting foreign keys in NDB?
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question