Tuesday, August 12, 2008

Generate unique primary key automatically in MySQL


How to generate unique primary key automatically for every record in MySQL?


Note: This question was asked by one anonymous visitor as a comment. Sharing the response as a separate article to make it more visible to our visitors.


I've not used MySQL for quite a while now. When I used MySQL some 3-4 years back I probably couldn't find any direct support for Sequences in MySQL (unlike Oracle) and hence I used the AUTO_INCREMENT feature which served my purpose of automatic unique ID generation.


What I could interpret from your question that you simply want a mechanism to generate unique numbers which will serve as primary keys of the records being inserted in the table. If this is the case then it's very easy to implement using the AUTO_INCREMENT feature only. You just need to append the 'AUTO_INCREMENT' clause to the primary key column of the table. For example:-


CREATE TABLE table_name(

id INT PRIMARY KEY AUTO_INCREMENT,

column_name1 ...,

...);


Once the table is created you can set the initial value of the AUTO_INCREMENT counter by using the following statement:-


ALTER TABLE table_name AUTO_INCREMENT = value;


You can of course not specify a value less than or equal to any value that has already been used for that table. In such a case MyISAM resets the AUTO_INCREMENT counter to the current maximum value plus one. InnoDB might also take care of it accordingly. But it's always better to avoid setting such a value and the start value is normally set in the very beginning before any insertion. From there on MySQL engine automatically increments the current maximum value by one to get the new unique number. The default value of the AUTO_INCREMENT counter is 1.


You can use LAST_INSERT_ID() to get the unique number generated by the previous INSERT opration executed on a table which had an AUTO_INCREMENT column. You can even pass an expression as an argument to the LAST_INSERT_ID() function. In this case the value of the argument will be returned by the function and the same value is remembered to be returned by the next invocation of the LAST_INSERT_ID() function. You may use this to create a table serving as a Sequence:-


CREATE TABLE sequence_table(

seq INT NOT NULL

);


Initialize the sequence counter by inserting a record:-


INSERT INTO sequence_table VALUES(100);


Updating the sequence and reading the value:-


UPDATE sequence_table SET seq = LAST_INSERT_ID(seq + 1);

SELECT LAST_INSERT_ID();


These are probably the two most commonly used mechanisms of implementing sequences. I guess the former suits your requirement better in most of the cases.



Share/Save/Bookmark


6 comments:

Anonymous said...

hi,i have a situation in which i have to save shoppingcart with a number and later on this number is used as link to show the shoppingcart of the user plz help me with proper example.

Anonymous said...

hi,how can i write code that
return resultset of more than one row

Geek said...

Well... this depends upon the actual requirements. Say for example if you want to restrict the maximum number of products to 10 that can be added to a Shopping Cart then you may like to have 1 (for id - auto generated) + 2*10 (for a max of 10 products and their respective quantities) + 1 (user_id of the customer) + 1 (current timestamp) + ... may be few more columns to capture some other relevant information.

Now you just need to save the product_id-quantity pairs for all the products added to the card while saving the cart. That's it. Whenever a user logs into the system you can simply query for all the saved carts for his user_id. The fetched result set can be used to form the data structure which will hold the data and the ids can now be easily made hyperlinks.

For Example: You may PRODUCTS table with these entries:-

PRODUCT_ID PRODUCT_NAME PRICE ...
10001 VanHeusenShirt 200
10002 LPShirt 300
...

Now in this case your SHOPPING_CART table may have entried like:-

SC_ID P1_ID P1_Q P2_ID P2_Q ...
400001 10001 3 10002 5
400002 10002 4 10001 6
...

Sample design of the shopping_cart table design in MySQL:-

CREATE TABLE shopping_cart(
id INT PRIMARY KEY AUTO_INCREMENT,
product_id_1 INT NOT NULL,
product_1_quantity INT NOT NULL,
product_id_2 INT,
product_2_quantity,
...
...
user_id INT NOT NULL,
current_timestamp TIMESTAMP
...
);

Note that the first product-quantity pair has been made NOT NULL here as a shopping cart should have at least one such pair. Otherwise no pint saving such a cart...right? user_id should of course be NOT NULL for the obvious reason.

ResultSet will automatically hold all the rows returned from the SELECT query. You just need to iterate through all the rows using the next() method (typically inside a while loop). While creating the Statement you can specify whether the ResultSet will be Scrollable and Concurrenyly Updatable or not. But you will probably not need to bother about such details for the simple situation you're talking about.

You have methods like absolute(int) for directly going to the particular row in the ResultSet and getters for almost every data type like getByte(), getBoolean, getString(), getBigDecimal(), getDate(), ... etc. You just need to pass the column name to these methods to fetch the values. You don't can easily get the MetaData by using the getMetaData() method in case the column names are not known at the design time which again doesn't look to be applicable in your case. You simply need to pass the column names to the corresponding getters based on their data type and get the value.

Anonymous said...

thanks,its really nice i am very greatful to

Devi said...

HI... Pls Anyone can u help me
What the Main Differences between primary key and unique Key.
IF possible can u gv me Examle Using MYSQL

Thanku So much
CHinuku

Geek said...

Hi Chinuku,

A combination of one or more columns of a database table which can uniquely identify all the rows of that table is called a candidate key. Naturally, a table may have more than one candidate keys and one of these candidate keys are selected as a primary key depending upon the actual requirement. For example: if we have a US based Employee table then we can easily think of two candidate keys - Employee ID and the SSN. Both of these keys are expected to uniquely identify all the rows of the Employee table. Normally the Employee ID field is selected as the primary key in such a situation for various reasons - one of them being the privacy protection (or maybe some other reason).

All the candidate keys except the primary key are called Alternate Keys or Unique Keys. So what's the difference between a primary key and an alternate key (or a unique key)? Do we really have any difference(s)? Yeah... primarily there are two differences. The main difference is that while an alternate key (or a unique key) allows NULL values, the primary key doesn't allow any NULL value as it's required to follow the NOT NULL constraint implicitly. Another noticeable difference is that one needs to declare a candidate key explicitly as the primary key whereas the alternate keys (or unique keys) don't really need any explicit declaration. They are just like any other data column(s).

Regarding the second part of your query - "possible example using MySQL", I would say that these concepts are not specific to any particular RDBMS. They hold good for all of them. Hope this helps. Let me know in case you have any further queries in this regard.