The
AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
AUTO INCREMENT Field. Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted. Are they also stored as the primary key at the database level. That's where you should stop, right there, and rethink. Your database primary key should NEVER have business meaning. It should be meaningless by definition. So add the GUID as your business key, and a normal primary key (usually a long int) as the database primary key. Aug 23, 2013 Oracle Java JDBC: Get Primary Key of Inserted record. We also have an oracle sequence called STUDENTSEQ that we uses to generate primary key for STUDENTS table. CREATE TABLE STUDENTS. And if your primary key is not generated by the SQL “studentid.nextval” but a trigger from the insert statement? Sep 26, 2018 A recent request made me think about Postgres’ INSERT. RETURNING clause, which is probably the most intuitive and concise way of returning generated keys from an insert statement. The importance of doing that becomes clear in the context of a jOOQ UpdatableRecord, which, when inserted, should refresh its IDENTITY, or Primary Key value.
Which returns:
![]()
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, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. For example:
If the column is declared
NOT NULL , it is also possible to assign NULL to the column to generate sequence numbers. For example:
When you insert any other value into an
AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:
Updating an existing
AUTO_INCREMENT column value in an InnoDB table does not reset the AUTO_INCREMENT sequence as it does for MyISAM and NDB tables.
You can retrieve the most recent automatically generated
AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
Use the smallest integer data type for the
AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the UNSIGNED attribute if possible to allow a greater range. For example, if you use TINYINT , the maximum permissible sequence number is 127. For TINYINT UNSIGNED , the maximum is 255. See Section 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” for the ranges of all the integer types.
For a multiple-row insert,
LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.
To start with an
AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE , like this:
For information about
AUTO_INCREMENT usage specific to InnoDB , see Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.
More information about
AUTO_INCREMENT is available here:
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |