469,903 Members | 1,501 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Alter a primary key auto increment column?

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;
Jul 20 '05 #1
1 27984
"Lannsjo" <la*****@home.se> wrote in message
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.
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.livegroup_id an int.

If that doesn't work

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

If that doesn't work

(1a) drop the foreign key relationship on livegroups.livegroup_id =
livegroup.id.
(1b) drop the index on livegroup_id.
(1c) alter table livegroups and make livegroups.livegroup_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.
ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; 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;

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by me | last post: by
3 posts views Thread by Metal Dave | last post: by
2 posts views Thread by RamaKrishna Narla | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.