Menu
For example I am creating the following table:
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
PRIMARY KEY (ID)
)
As I know, the value of Auto increment can be modify by Alter table like this
ALTER TABLE Persons AUTO_INCREMENT=100;
But I just want to know is there a way to set the value of Auto Increment while creating the table?
Somthing like this?
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT = 100,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
PRIMARY KEY (ID)
)
506
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
Obtaining the value of column that uses AUTO_INCREMENT after an INSERT statement can be achieved in a number of different ways. To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function.
Go to Identity Specifications and explore it. Make (Is Identity) row as Yes and by default Identity Increment row and Identity Seed row become 1. In case we want to automatically increase the value of this column by 2 (like 1, 3, 5, 7 etc.) then change the value of Identity Seed to 2.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
Yes you can do it while creating the table as
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
PRIMARY KEY (ID)
)AUTO_INCREMENT=100;
Here is a test case
mysql> CREATE TABLE Persons (
-> ID int NOT NULL AUTO_INCREMENT,
-> LastName varchar(255) NOT NULL,
-> FirstName varchar(255),
-> Address varchar(255),
-> PRIMARY KEY (ID)
-> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into Persons (LastName) values ('CCC');
Query OK, 1 row affected (0.03 sec)
mysql> select * from Persons ;
+-----+----------+-----------+---------+
| ID | LastName | FirstName | Address |
+-----+----------+-----------+---------+
| 100 | CCC | NULL | NULL |
+-----+----------+-----------+---------+
1 row in set (0.00 sec)
try this
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
PRIMARY KEY (ID)
)AUTO_INCREMENT=100;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
