Submitted by Kiran on Wed, 04/29/2009 - 10:56
Currently the MyISAM storage engine has no support for foreign keys, but is there any way to put the logic for foreign key checking in a stored procedure.
Bookmark/Search this post with:
»
- 1149 reads
It is really simple to insert the logic for foreign key checking in a stored procedure.
Example of a sample store procedure is:
BEGIN
DECLARE v INT;
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
SET v = 0;
IF p_fk IS NOT NULL THEN
SELECT 1 INTO v FROM tpk WHERE cpk = p_fk LIMIT 1;
INSERT INTO tfk VALUES (p_fk, p_animal);
ELSE
SET v = 1;
END IF;
END;
IF v <> 1 THEN
DROP TABLE `The insertion failed`;
END IF;
END;
Explanation:
You are going to get either an SQLEXCEPTION or a NOT FOUND condition will cause v to be zero, but otherwise v will be one, because the SELECT will put one into it and the EXIT HANDLER won't
set it back to zero.
The store procedure call result:
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE tfk (cfk INT, canimal VARCHAR(10));//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tpk VALUES (1),(7),(10);//
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CALL foreignkey_insert(1,'wombat');//
Query OK, 1 row affected (0.02 sec)
mysql> CALL foreignkey_insert(NULL,'wallaby');//
Query OK, 0 rows affected (0.00 sec)
mysql> CALL foreignkey_insert(17,'wendigo');//
ERROR 1051 (42S02): Unknown table 'The insertion failed'
keep rocking !!
Post new comment