SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL PHP MySQL Functions

MySQL auto incremented field to generate unique ID for the record

We can get a unique auto generated number from MySQL by creating an auto incremented field. MySQL will generate a unique number by incrementing the last number of the table and will automatically add to the auto incremented field. This is the best way to generate a trouble ticket number for a help desk system. In a school if a new student joins then all details of the student we can feed to student table and while using the insert query, MySQL will add one auto generated unique number to this auto incremented field. We need not have to specify any thing in our query while adding the other details ( except auto incremented field data).

Getting the unique ID

After successfully adding the record we can get this unique number by using mysql_insert_id(). Read the details on how to get this number by visiting the tutorial here. Now let us try how to create a such auto incremented field.

We have to make auto increment field integer ( obvious ) and also unique. The property set to auto increment. Here is the sql to create a student table with one auto increment field to assign one unique student ID.
CREATE TABLE `student` ( 
`student_id` INT( 3 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL ,
UNIQUE ( 
`student_id` 
)
);
Now we will add one record to this table using one insert command.
INSERT INTO `student` (  `name` , `email` ) 
VALUES (  'john', 'john@sitename.com' );
You will see automatically the student_id field will be added with 1 and for next record it will get the next incremented value 2.

This way we can generate auto increment id for each record in our table.

Maximum value of inserted id

The maximum value of the inserted id depends on the type of numeric field we have selected. For TINYINT it will take value upto 127 for unsigned it is 255. After this the creation of auto increment ID will fail. So take care while creating the auto increment field to match your future requirements.

Starting from higher number

Change the auto increment field like this

ALTER TABLE student AUTO_INCREMENT = 50;

Read how we can alter table and make one numeric field as auto increment

Read here on how to create one incremental auto generated record number in MSSQL table.

Number of User Comments : 3


Google+

More on PHP MySQL functions
Senthu07-12-2009
How to export the tables in .sql format from phpmysql db by using in phpMySQL ??
Thank you
SENTHU
dhananjay13-09-2010
goto phpmyadmin, select database , select table using checkbox, go downward select option mysql then zip and click on go it will ask you where u want to save your table .
Nitin09-11-2011
is it compulsory to define auto increment field unique or primary key constraint ?
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer