vijji_lakshmi
asked on
foreign key support for NDBCLUSTER
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
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
ASKER
can you please provide with the examples link.where can i refer how they done.
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_row s($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_row s($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.
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_row
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_row
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.
ASKER
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.
can you please let me know how to use it with java??
please reply me soon it is very urgent.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It is a bit more work, and it will slow down your application a little, but it can be done.