-1

I have been using AUTO_INCREMENT attribute to generate a primary key when a new row is inserted into a table in MySQL.

Could somebody help me understand what is the default primary key generation strategy for MySQL and how does it work?

EDIT Hibernate has a identifier generation strategy native selects identity, sequence or hilo depending upon the capabilities of the underlying database. I used MySQL with hibernate.hbm2ddl.auto=update which generated id BIGINT(20) NOT NULL AUTO_INCREMENT for id property of Long Java data type.

I am trying to understand how did Hibernate choose AUTO_INCREMENT when it used SchemaExport tool. Is AUTO_INCREMENT the default primary key generation strategy for MySQL?

skip
  • 12,193
  • 32
  • 113
  • 153
  • Answer given at http://stackoverflow.com/questions/23056660/hibernate-how-does-native-identifier-generation-strategy-work/23071465?noredirect=1#23071465 – skip Apr 15 '14 at 07:46

3 Answers3

1

From here

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

If no value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers. If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.

The value will be incremented for each new row

The value is unique, duplicates are not possible

If a row is deleted, the auto_increment column of that row will not be re-assigned.

The auto_increment value of the last inserted row can be accessed using the mySQL function LAST_INSERT_ID() but it must be called right after the insert query, in the same database connection

Paul Bele
  • 1,514
  • 1
  • 11
  • 12
0

Always not necessary to use auto increment to put it as primary key. 

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (P_Id)
)
Rahul
  • 211
  • 2
  • 10
0

You can use 'SERIAL' data type.

CREATE TABLE test(
 "id" Serial NOT NULL,
);

ALTER TABLE "test" ADD CONSTRAINT "Key1" PRIMARY KEY ("id");

Andrzej Reduta
  • 767
  • 1
  • 7
  • 15