Great!! Thx I looked into the manual and the refer to the create syntax concerning auto_increment. The one thing I did not get was the fact I have to 'recreate' the column definition and can not just add something to it.
It works great now! The for the effort of replying :) Greetings, Reinhart Viane Btw, how do you show your tables layout like you did in this mail? -----Oorspronkelijk bericht----- Van: Michael Stassen [mailto:[EMAIL PROTECTED] Verzonden: woensdag 1 juni 2005 16:47 Aan: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Onderwerp: Re: change field to auto increment 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 -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 267.3.3 - Release Date: 31/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
