List:General Discussion« Previous MessageNext Message »
From:Richard - CEDRICOM Date:October 8 2004 10:08am
Subject:Naming constraints
View as plain text  
Hi,

I have problem in naming foreign key constraints :

Engine : mySql 4.1.1 and/or 5.0.0a
System : Windows 2000 sp4 and / or win NT 4 sp6a


Here is a script example of what I need to do :

/*  Script 1 */

CREATE TABLE tblphone (
  nameid   INT PRIMARY KEY AUTO_INCREMENT,
  fname    VARCHAR(30),
  lname    VARCHAR(30) NOT NULL
) TYPE = INNODB;


CREATE TABLE tblnumbers(
   numid   INT PRIMARY KEY AUTO_INCREMENT,
   nameid  INT ,
   phone   VARCHAR(20),
   INDEX(nameid),
    CONSTRAINT fktblnumb    FOREIGN KEY(nameid)    REFERENCES
tblphone(nameid)
) TYPE = INNODB;


The aim of this script is to use a defined name for the foreign key
constraint (here 'fktblnumb') and use it for dropping the constraints in
another update scripts :

/* Script 2 */
alter table tblnumbers
    drop foreign key fktblnumb;


Actually, the name for the just created constraint is an 'internally
generated id'  (i.e :0_023), so the update script can't find the named
constraint 'fktblnumb'


The "show create table tblnumbers" outputs :


CREATE TABLE `tblnumbers` (
  `numid` int(11) NOT NULL auto_increment,
  `nameid` int(11) default NULL,
  `phone` varchar(20) default NULL,
  PRIMARY KEY  (`numid`),
  KEY `nameid` (`nameid`),
  CONSTRAINT `0_23` FOREIGN KEY (`nameid`) REFERENCES `tblphone` (`nameid`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Any idea about this behaviour ?

thanks in advance...

best regards


--
Richard FURIC
CEDRICOM
Tel : 02 99 55 07 55
Fax : 02 99 55 08 64
E-mail : etudes@stripped
site vitrine : www.cedricom.com


Thread
Naming constraintsRichard - CEDRICOM8 Oct
  • Re: Naming constraintsMartijn Tonies8 Oct
  • Re: Naming constraintsRichard - CEDRICOM8 Oct
  • Re: Naming constraintsMartijn Tonies8 Oct