List:General Discussion« Previous MessageNext Message »
From:kayamboo Date:December 6 2002 7:15am
Subject:Re: alter table syntax
View as plain text  
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" <hannes.niedner@stripped>
To: "MySQL Mailinglist" <mysql@stripped>
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" <kayamboosuresh@stripped> 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 <mysql-thread126783@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-hannes.niedner=gmx.net@stripped>
> > 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 <mysql-thread126784@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-kayamboosuresh=softhome.net@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

Thread
alter table syntaxkayamboo6 Dec
  • Re: alter table syntaxR. Hannes Niedner6 Dec
    • Re: alter table syntaxkayamboo6 Dec
      • re: Re: alter table syntaxEgor Egorov6 Dec
      • Re: alter table syntaxDan Nelson6 Dec
    • Re: alter table syntaxkayamboo9 Dec
      • Re: alter table syntaxDan Nelson9 Dec
    • Re: Re: alter table syntaxkayamboo9 Dec
    • Re: alter table syntaxListen Hinz9 Dec