Sorry, I tried that before.
***********************************************************
This is my show create table syntax
mysql> show create table town_db ;
+---------+-----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
| Table | Create Table
+---------+-----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
| town_db | CREATE TABLE `town_db` (
`town` varchar(50) NOT NULL default '',
`subcity` varchar(50) NOT NULL default '',
`rankt` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`town`),
KEY `subcity` (`subcity`),
FOREIGN KEY (`subcity`) REFERENCES `forest.subcity_db` (`subcity`) ON
DELETE CASCADE
) TYPE=InnoDB |
+---------+-----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
1 row in set (0.00 sec)
***********************************************************
I used this to create the table
CREATE TABLE TOWN_DB( town VARCHAR(50) NOT NULL PRIMARY KEY, subcity
VARCHAR(50) NOT NULL, rankt INT UNSIGNED NOT NULL,
INDEX( subcity ) ,
FOREIGN KEY( subcity )
REFERENCES subcity_db( subcity ) ON DELETE CASCADE) TYPE = INNODB ;
*************************************************************
When I tried to drop the key, this was the error
mysql> alter table town_db drop primary key ;
ERROR 1025: Error on rename of '.\forest\#sql-f6_1' to '.\forest\town_db'
(errno: 150)
I suppose some lock in the index somewhere but couldnot figure it out.
regards
----- Original Message -----
From: "R. Hannes Niedner" <[EMAIL PROTECTED]>
To: "MySQL Mailinglist" <[EMAIL PROTECTED]>
Sent: Friday, December 06, 2002 3:50 PM
Subject: Re: alter table syntax
> ALTER TABLE town_db1 DROP PRIMARY KEY;
> ALTER TABLE town_db1 ADD PRIMARY KEY(town, subcity );
>
> On 12/5/02 10:31 PM, "kayamboo" <[EMAIL PROTECTED]> wrote:
>
> > sql, mysql, query
> >
> > Hello folks
> >
> > This is my table structure with MySql 4.0.3-beta-max-nt with InnoDB
> >
> > +---------+------------------+------+-----+---------+-------+
> > | Field | Type | Null | Key | Default | Extra |
> > +---------+------------------+------+-----+---------+-------+
> > | town | varchar(50) | | PRI | | |
> > | subcity | varchar(50) | | MUL | | |
> > | rankt | int(10) unsigned | | | 0 | |
> > +---------+------------------+------+-----+---------+-------+
> > 3 rows in set (0.01 sec)
> >
> > Now I want to have the primary key on both town and subcity fields like
this
> >
> > mysql> describe town_db1 ;
> > +---------+------------------+------+-----+---------+-------+
> > | Field | Type | Null | Key | Default | Extra |
> > +---------+------------------+------+-----+---------+-------+
> > | town | varchar(50) | | PRI | | |
> > | subcity | varchar(50) | | PRI | | |
> > | rankt | int(10) unsigned | | | 0 | |
> > +---------+------------------+------+-----+---------+-------+
> > 3 rows in set (0.00 sec)
> >
> > I am able to create new table but can't alter the existing one to
achieve
> > this.
> >
> >
> > Any suggestions with alter syntax is highly appreciated
> >
> > Thanks for your time
> >
> > Regards
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
