Drop Foreign Key In Mysql 5.1.9

[ashokkumar]

hi..

i am new to mysql 5. I created one table with foreign key field. Then i tried to drop the foreign key from the table by using the Query: " alter table <table name> drop foreign key <foreign key name>;" . But, foreign key is not dropped from the table,but query executed sucessfuly.

What is way to drop the foreign key from the table.
If any one know the solution... Please tell me.... it is very urgent and important one...

What is query to drop the foreign key from the table in
MySQL 5.1.9 version.?

Reply immediately..

Regards,
S.Ashokkumar
India.

[oraclable]

Sorry for this late answer:
Make sure you use Innodb as Engine when you want create foreign keys.

Here is a complete example:
-------------------------------------------------------------

DROP TABLE IF EXISTS `parent`;
CREATE TABLE `parent` (
`p_id` int(10) PRIMARY KEY,
`c_otherfield` char(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;


DROP TABLE IF EXISTS `child`;
CREATE TABLE `child` (
`c_id` int(10) default NULL,
`p_id` int(10) NOT NULL,
`c_otherfield` char(10) default NULL,
FOREIGN KEY (`p_id`) REFERENCES parent(`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Execute the command below :
SHOW CREATE TABLE child;

And you will see this line:
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `parent` (`p_id`)

Now Execute:
ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

and the foreign key is gone for ever.

------------------------------------------------------------
Good luck.