List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:February 2 2004 10:00am
Subject:Re: Why does MySQL generate internal foreign key id when constrain name
View as plain text  
Philip,

----- Original Message ----- 
From: "Philip Walden" <pwalden@stripped>
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/

Thread
Re: Why does MySQL generate internal foreign key id when constrain nameHeikki Tuuri2 Feb