General Note:

Here we have pulled in the data from a sample database. You can download it from the following link and use it for practicing Click Here For Sample Database

AUTOINCREMENT

Autoincrement keyword is used to generate unique number for all row in table starting from the value 1. We have to use the autoincrement keyword while creating tables. Here we going to create a table for product, which contains product id and product name.


Create table product(product_id int primary key autoincrement,product_name text)

The query will create product table.Auto increment only works with primary key column. If you use autoincremnt keyword without primary key column the compiler will throw an error. At the same you must use integer datatype. The insert queries are below.


Insert into product(product_name) values(‘p1’)

Insert into product(product_name) values(‘p2’)

Insert into product(product_name) values(‘p3’)

Insert into product(product_name) values(‘p4’)

Insert into product(product_name) values(‘p5’)

This will generate sequence of numbers from 1 to 5. The below query will display the inserted data


Select * from product;

product_id product_name

---------- ------------

1          p1    

2           p2    

3           p3        

4           p4        

5           p5        

If you remove any row from table it will continue the number generation from last generated value. If I remove the row which contains product id 2 and adding another row . it will generate the product id as 6 not 5.


Delete from product where product_id=2;

Insert into product(product_name) values(‘p6’);

 

Select * from product;

product_id product_name

---------- ------------

1           p1        

3           p3        

4           p4        

5           p5        

6           p6