Alter a primary key auto increment column?

[Lannsjo]

I need to change my primary key column type from smallint to int.
I have tried:
ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;
But get an error message certainly since my id-column is primary key
and references other tables as well.

How can I come around this problem?
Need help

/Martin

This is my table definition

livegroup (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
type VARCHAR(60) NOT NULL,
name CHAR(60) NOT NULL,
public TINYINT NOT NULL,
creator SMALLINT UNSIGNED NOT NULL,
lastmodified TIMESTAMP(8),
PRIMARY KEY (id),
INDEX (creator),
FOREIGN KEY (creator) REFERENCES user (id) ON DELETE CASCADE
) TYPE=INNODB;

livegroups (
data_id SMALLINT UNSIGNED NOT NULL,
livegroup_id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (data_id, livegroup_id),
INDEX (data_id),
INDEX (livegroup_id),
FOREIGN KEY (data_id) REFERENCES livedata (id) ON DELETE CASCADE,
FOREIGN KEY (livegroup_id) REFERENCES livegroup (id) ON DELETE
CASCADE
) TYPE=INNODB;

[Siemel Naran]

Re: Alter a primary key auto increment column?

"Lannsjo" <lannsjo@home.s e> wrote in message
[color=blue]
> I need to change my primary key column type from smallint to int.
> I have tried:
> ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;
> But get an error message certainly since my id-column is primary key
> and references other tables as well.[/color]

Just thinking out aloud, what if you alter the table that uses the id-column
as a foreign key? Here are the steps I'd try:

(1) alter table livegroups and make livegroups.live group_id an int.

If that doesn't work

(1a) drop the foreign key relationship on livegroups.live group_id =
livegroup.id.
(1b) alter table livegroups and make livegroups.live group_id an int.

If that doesn't work

(1a) drop the foreign key relationship on livegroups.live group_id =
livegroup.id.
(1b) drop the index on livegroup_id.
(1c) alter table livegroups and make livegroups.live group_id an int.

(2) Similarly, alter table livegroup to make livegroup.id an int. Try the
version where you just change the column, if that doesn't work drop the
primary key then alter the column.

(3) In the base table livegroup add back the index if you had to drop it.

(4) In the dependent tables add back the index and foreign key relatinoship
if you had to drop it.
[color=blue]
> ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;[/color]
[color=blue]
> How can I come around this problem?
> Need help
>
> /Martin
>
> This is my table definition
>
> livegroup (
> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
> type VARCHAR(60) NOT NULL,
> name CHAR(60) NOT NULL,
> public TINYINT NOT NULL,
> creator SMALLINT UNSIGNED NOT NULL,
> lastmodified TIMESTAMP(8),
> PRIMARY KEY (id),
> INDEX (creator),
> FOREIGN KEY (creator) REFERENCES user (id) ON DELETE CASCADE
> ) TYPE=INNODB;
>
> livegroups (
> data_id SMALLINT UNSIGNED NOT NULL,
> livegroup_id SMALLINT UNSIGNED NOT NULL,
> PRIMARY KEY (data_id, livegroup_id),
> INDEX (data_id),
> INDEX (livegroup_id),
> FOREIGN KEY (data_id) REFERENCES livedata (id) ON DELETE CASCADE,
> FOREIGN KEY (livegroup_id) REFERENCES livegroup (id) ON DELETE
> CASCADE
> ) TYPE=INNODB;[/color]