469,579 Members | 1,195 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Why does MySQL generate internal foreign key id when constrain nameis given?

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.

Thanks

Phil
Jul 19 '05 #1
0 1542

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by aaron | last post: by
1 post views Thread by Thomas T. Thai | last post: by
14 posts views Thread by Anoop | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.