Philip, ----- Original Message ----- From: "Philip Walden" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc 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 replication of 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: `databasename.tablename_fk_<nth_generated_constraint_for_this_table>` 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 the master. I hope these bug fixes make it to 4.0.18 and 4.1.2. > Thanks > > Phil Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
