MySQL Forums
Forum List  »  InnoDB

Re: Drop foreign key only if it exists
Posted by: James Rivord
Date: February 11, 2009 01:32PM

This is a good, but I wanted to make this a generic procedure. I changed it to be like below.

DELIMITER $$

DROP PROCEDURE IF EXISTS DropFK $$
CREATE PROCEDURE DropFK (
IN parm_table_name VARCHAR(100),
IN parm_key_name VARCHAR(100)
)
BEGIN
IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN
ALTER TABLE parm_table_name DROP FOREIGN KEY parm_key_name;
END IF;
END $$
DELIMITER ; $$

The problem I am running into is that the alter table command tells me that the table __database_name__.parm_table_name does not exist. Everything works exactly as I would expect. If I replace the alter table with a select, I get what I expect. I am not sure if this is a bug or I am formatting the procedure wrong. Any help would be appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Drop foreign key only if it exists
19244
February 11, 2009 01:32PM
12916
February 12, 2009 10:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.