Member Avatar for somjit{}

im trying to do this :

alter table project_info change column number proj_id int not null auto_increment,
add primary key (proj_id);

but mysql tells iv got an error. any help with whats wrong here ? i followed this from head first sql book (page 210 if anyone's interested ) .

  • 2 Contributors
  • 2 Replies
  • 318 Views
  • 25 Minutes Discussion Span
  • Latest Post Latest Post by somjit{}

Dani AI

Generated

Quick clarification and a few practical tips that complement the replies from and .

CHANGE vs MODIFY vs RENAME COLUMN — purpose and behavior: CHANGE can rename a column and change its definition at the same time, but it requires the old and new names and the full new column definition. MODIFY only changes the column definition (not the name). RENAME COLUMN (added in MySQL 8.0) is a convenience to rename without restating the definition. The COLUMN keyword is optional in many ALTER clauses (so the form without it is equivalent). (dev.mysql.com)

Safer, version-aware workflow (avoid surprising attribute loss):

  • Inspect the current definition first:
SHOW CREATE TABLE project_info;
  • On MySQL 8.0+: rename cleanly, then set type/auto_increment and add the key in separate steps:
ALTER TABLE project_info RENAME COLUMN `number` TO `proj_id`;

ALTER TABLE project_info MODIFY COLUMN `proj_id` INT NOT NULL AUTO_INCREMENT;
ALTER TABLE project_info ADD PRIMARY KEY (`proj_id`);

Using separate statements keeps each change explicit and makes it easier to see/undo mistakes on older servers. For pre-8.0 servers use CHANGE, but be sure to copy the complete column definition from SHOW CREATE TABLE when using CHANGE (attributes not restated will be lost). (dev.mysql.com)

AUTO_INCREMENT and primary-key notes (common causes of errors): AUTO_INCREMENT only applies to integer types, there can be one per table, and the column must be part of an index (usually the primary key). Attempting to add a PK will fail if duplicate or NULL values exist in the column. Before adding the key, verify uniqueness and NULLs (compare total rows vs distinct values) and back up the table. (dev.mysql.com)

If an ALTER still errors, capture the exact error text, server version, storage engine, and the output of SHOW CREATE TABLE / SHOW WARNINGS — that information is what debuggers need.

Recommended Answers

All 2 Replies

Member Avatar for Szabi Zsoldos

Use MODIFY instead of CHANGE

Member Avatar for somjit{}

modify is for changing the type only right ? im trying to change the name and the type together...

this place shows that the keyword column is not needed as iv done above , but that also is giving errors.

edit:

alter table project_info change number proj_id int not null auto_increment,
add primary key (proj_id);

this seems to get the job done .
thanks for helping :)

Edited by somjit{}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.