Reinhart Viane wrote:
Hey list,
I need a query that checks if a certain table column has type auto-increment
If not set it to auto increment, else do nothing.
Let's say I have the table objects (object_id, object_name) in which
object_id is the primary field.
Now check to see if object_id is auto_increment and if not set it
auto_increment
Can anyone help me on this? I've been looking into the manual but I can't
get it right:
Alter table objects change object_id type auto_increment
Thx in advance,
Reinhart
You can find out if objects.object_id is set to AUTO_INCREMENT in a
couple of ways.
Use "DESCRIBE tablename"
<http://dev.mysql.com/doc/mysql/en/describe.html> to see info about all
the columns:
mysql> DESCRIBE objects;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| name | char(30) | YES | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
or use "DESCRIBE tablename colname" to see info about a specific column:
mysql> DESCRIBE objects id;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.01 sec)
or use "SHOW CREATE TABLE tablename"
<http://dev.mysql.com/doc/mysql/en/show-create-table.html>:
mysql> SHOW CREATE TABLE objects\G
*************************** 1. row ***************************
Table: objects
Create Table: CREATE TABLE `objects` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` char(30) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
The last is probably best for your purposes.
To change a column definition using ALTER TABLE, you have to give the
complete new column definition, you can't simply add an attribute. If
objects.id in my example above had not been AUTO_INCREMENT:
ALTER TABLE objects
CHANGE id id int(10) unsigned NOT NULL auto_increment;
That is, I took the line from "SHOW CREATE TABLE" for id, added the
AUTO_INCREMENT attribute, and used that as the new column definition.
See the manual for details on ALTER TABLE syntax
<http://dev.mysql.com/doc/mysql/en/alter-table.html>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
