----- Original Message -----
From: "Philip Walden" <pwalden@stripped>
Sent: Monday, February 02, 2004 3:39 AM
Subject: Why does MySQL generate internal foreign key id when constrain name
> I am using MySQL 4.1.1-1.
> When I add a "named" foreign key constraint
> alter table sb_query_nm_sub_tp
> add constraint f1sbquerynmsubtp foreign key (query_nm) references
> sb_query_class (query_nm);
> And then do a show create table:
> | sb_query_nm_sub_tp | CREATE TABLE `sb_query_nm_sub_tp` (
> `query_nm` varchar(32) NOT NULL default '',
> `sub_tp_tree_nm` varchar(32) NOT NULL default '',
> `sub_tp_nm` varchar(32) NOT NULL default '',
> `updt_user_id` smallint(6) NOT NULL default '0',
> `updt_dt` date NOT NULL default '0000-00-00',
> `create_dt` date NOT NULL default '0000-00-00',
> PRIMARY KEY (`query_nm`),
> CONSTRAINT `0_1584` FOREIGN KEY (`query_nm`) REFERENCES
> `sb_query_class` (`query_nm`)
> ) TYPE=InnoDB DEFAULT CHARSET=latin1 |
> I get an internally generated name where f1sbquerynmsubtp <> 0_1584.
> To drop the foreign key, I have to use the internal id which is only
> retrievable from the "show create table". I have some legacy code I am
> trying to port from another RDBMS. This code adds/drops constraints and
> expects the constraint to be named as specified in the "add".
> How would I code to find the internal constraint id right after the
> "add"? Or is there a way to get MySQL to use the constraint name that is
> given. This seems like a significant deficiency in MySQL.
I am working on fixing this problem, along with another one in the
ALTER TABLE ... DROP FOREIGN KEY ...;
The solution would be to remember the foreign key constraint name, if given
by the user. If the user does not supply a name, then we would generate an
id of the form:
Replication does not like globally generated id's like `0_1584`, because the
id's will differ if the slave is not set up as an exact binary replica of
I hope these bug fixes make it to 4.0.18 and 4.1.2.
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
Order MySQL technical support from https://order.mysql.com/
|• Re: Why does MySQL generate internal foreign key id when constrain name||Heikki Tuuri||2 Feb|