How does one drop a foreign key from a InnoDB table in mySQL 4.1.x?
The manual says
"ALTER TABLE yourtablename DROP FOREIGN KEY
internally_generated_foreign_key_id
You have to use SHOW CREATE TABLE to determine the internally generated
foreign key ID when you want to drop a foreign key."
However, my CREATE TABLE statment in 4.1.0 does not return an "intertnally
generated foreign key ID", but something like this:
mysql> SHOW CREATE TABLE cm_mail \G
*************************** 1. row ***************************
Table: cm_mail
Create Table: CREATE TABLE `cm_mail` (
`id` int(15) NOT NULL auto_increment,
`view` varchar(30) NOT NULL default '',
`touser` varchar(30) NOT NULL default '',
`from2` varchar(30) NOT NULL default '',
`message` mediumtext NOT NULL,
`datestamp` int(11) NOT NULL default '0',
`status` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `id_2` (`id`),
KEY `touser_idx` (`touser`),
KEY `from_idx` (`from2`),
FOREIGN KEY (`touser`) REFERENCES `usertable` (`name`)
) TYPE=InnoDB CHARSET=latin1
A quick search on Google shows that 4.0.x users got something like
"CONSTRAINT '0_22'" before the FOREIGN KEY declaration and were able to use
that number. Any idea how I get this? I'd really like to drop this key so I
can re-add it with an ON DELETE and ON UPDATE clause; :-)
Thanks,
Joshua Thomas
Network Operations Engineer
PowerOne Media, Inc.
tel: 518-687-6143
[EMAIL PROTECTED]
---
In theory there is no difference between theory and practice. In practice
there is.
- Yogi Berra
---
