{"id":11934,"date":"2024-04-29T04:16:42","date_gmt":"2024-04-29T04:16:42","guid":{"rendered":"https:\/\/mysqlcode.com\/?p=11934"},"modified":"2024-04-29T04:16:42","modified_gmt":"2024-04-29T04:16:42","slug":"sql-auto-increment","status":"publish","type":"post","link":"https:\/\/mysqlcode.com\/sql-auto-increment\/","title":{"rendered":"SQL Auto Increment (With Examples)"},"content":{"rendered":"\n

SQL\u00a0auto-increment\u00a0is a specific technique to create\u00a0some\u00a0unique numbers as a value to\u00a0be stored\u00a0in the tables.\u00a0Database records,\u00a0as a result, need specific primary keys while adding new rows to the database.\u00a0It can also be done\u00a0without any manual override with the help of the auto-increment feature.\u00a0This article gives a detailed definition of auto-increment or\u00a0key\u00a0uniqueness.\u00a0To keep it simple, we will begin with the intro and some practical implementation.<\/p>\n\n\n\n

How Does Auto Increment Work? <\/h2>\n\n\n\n

When \u0430 column\u00a0is set up\u00a0as auto incrementable, the database system itself generates and provides sequential integer value to such column every time the new record\u00a0is inserted.\u00a0This sequential integer usually starts from an initial value\u00a0which\u00a0is\u00a0specified and further generates, each new addition\u00a0being\u00a0produced\u00a0by the sum of the successive previous terms.\u00a0We all know auto-incremental columns\u00a0are used\u00a0as primary keys\u00a0which\u00a0carry along with the uniqueness of each record.\u00a0Streamline database management by acting on data structures setup due to developers not\u00a0has\u00a0to manually make primary\u00a0key\u00a0values<\/a>,\u00a0making the disputes or errors diminish.<\/p>\n\n\n\n

Also Read: Reset Auto Increment In MySQL<\/a><\/strong><\/em><\/p>\n\n\n\n

Examples of Auto Increment in SQL<\/h2>\n\n\n\n

Example 1:<\/h3>\n\n\n\n

In this implementation, we are using an SQL server. The\u00a0auto-increment can\u00a0be done\u00a0using the IDENTITY keyword. There\u00a0are two attributes for the IDENTITY keyword. The\u00a0first integer will tell the initial value of the unique ID\u00a0and\u00a0the second will\u00a0tell\u00a0the incrementation steps.\u00a0<\/p>\n\n\n\n

In\u00a0this example, we\u00a0are starting\u00a0the primary key with\u00a01\u00a0and\u00a0taking\u00a01 step forward.\u00a0<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> Table2 (\n<\/span><\/span>    id<\/span> INT<\/span> IDENTITY<\/span>(1<\/span>,1<\/span>) PRIMARY KEY<\/span>,\n<\/span><\/span>    name<\/span> VARCHAR<\/span>(50<\/span>)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> Table2 (name<\/span>) VALUES<\/span> ('X'<\/span>), ('Y'<\/span>), ('Z'<\/span>);\n<\/span><\/span>SELECT<\/span> * FROM<\/span> Table2;\n<\/span><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Auto
Auto Increment Example 1<\/figcaption><\/figure>\n\n\n\n
In the results, you can see the unique primary keys are assigned to each row.  <\/p>\n\n\n\n
Example 2:<\/h3>\n\n\n
CREATE<\/span> TABLE<\/span> Table1 (\n<\/span><\/span>    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n<\/span><\/span>    name<\/span> VARCHAR<\/span>(50<\/span>)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> Table1 (name<\/span>) VALUES<\/span> ('A'<\/span>), ('B'<\/span>), ('C'<\/span>);\n<\/span><\/span>SELECT<\/span> * FROM<\/span> Table1;\n<\/span><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example is based on Auto Increment where we are incrementing primary keys. The primary keys (unique IDs) are assigned for each new row. This is a simple and small example to understand the working of Auto Increment. <\/p>\n\n\n\n
\"Auto
Auto Increment Example 2<\/figcaption><\/figure>\n\n\n\n
In the result, you can see the unique ID is assigned to each name. <\/p>\n\n\n\n
Example 3:<\/h3>\n\n\n\n
In PostgreSQL, the SERIAL is used to increment the primary key in the table. Let’s implement the query and verify the results. <\/p>\n\n\n
CREATE<\/span> TABLE<\/span> Table3 (\n<\/span><\/span>    id<\/span> SERIAL<\/span> PRIMARY KEY<\/span>,\n<\/span><\/span>    name<\/span> VARCHAR<\/span>(50<\/span>)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> Table3 (name<\/span>) VALUES<\/span> ('P'<\/span>), ('Q'<\/span>), ('R'<\/span>);\n<\/span><\/span>SELECT<\/span> * FROM<\/span> Table3;\n<\/span><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Auto
Auto Increment Example 3<\/figcaption><\/figure>\n\n\n\n
You can see the outcome is correct. <\/p>\n\n\n\n
Example 4:<\/h3>\n\n\n\n
In this example, we\u00a0have created four different\u00a0tables in our database to manage the E-commerce platform. This\u00a0example implements the technique of auto-incrementation to generate unique primary integer values in each table of every entry.<\/p>\n\n\n\n
The\u00a0‘AUTO_INCREMENT’ keyword\u00a0is used\u00a0in this query. Let’s\u00a0see the final result to validate the query.\u00a0<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> products_table (\n<\/span><\/span>    product_id INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n<\/span><\/span>    name<\/span> VARCHAR<\/span>(100<\/span>),\n<\/span><\/span>    price DECIMAL<\/span>(10<\/span>, 2<\/span>),\n<\/span><\/span>    description TEXT<\/span>\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>CREATE<\/span> TABLE<\/span> customers_table (\n<\/span><\/span>    customer_id INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n<\/span><\/span>    name<\/span> VARCHAR<\/span>(100<\/span>),\n<\/span><\/span>    email VARCHAR<\/span>(100<\/span>)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>CREATE<\/span> TABLE<\/span> orders_table (\n<\/span><\/span>    order_id INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n<\/span><\/span>    customer_id INT<\/span>,\n<\/span><\/span>    order_date TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>,\n<\/span><\/span>    total_amount DECIMAL<\/span>(10<\/span>, 2<\/span>),\n<\/span><\/span>    FOREIGN<\/span> KEY<\/span> (customer_id) REFERENCES<\/span> customers_table(customer_id)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>CREATE<\/span> TABLE<\/span> order_items_table (\n<\/span><\/span>    order_item_id INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n<\/span><\/span>    order_id INT<\/span>,\n<\/span><\/span>    product_id INT<\/span>,\n<\/span><\/span>    quantity INT<\/span>,\n<\/span><\/span>    price DECIMAL<\/span>(10<\/span>, 2<\/span>),\n<\/span><\/span>    FOREIGN<\/span> KEY<\/span> (order_id) REFERENCES<\/span> orders_table(order_id),\n<\/span><\/span>    FOREIGN<\/span> KEY<\/span> (product_id) REFERENCES<\/span> products_table(product_id)\n<\/span><\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> products_table (name<\/span>, price, description) VALUES<\/span>\n<\/span><\/span>('Laptop'<\/span>, 999.99<\/span>, 'High-performance laptop with SSD storage'<\/span>),\n<\/span><\/span>('Smartphone'<\/span>, 699.99<\/span>, 'Latest smartphone with OLED display'<\/span>),\n<\/span><\/span>('Headphones'<\/span>, 149.99<\/span>, 'Wireless noise-canceling headphones'<\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> customers_table (name<\/span>, email) VALUES<\/span>\n<\/span><\/span>('A'<\/span>, 'A@example.com'<\/span>),\n<\/span><\/span>('B'<\/span>, 'B@example.com'<\/span>);\n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> orders_table (customer_id, total_amount) VALUES<\/span>\n<\/span><\/span>(1<\/span>, 1099.98<\/span>),  \n<\/span><\/span>(2<\/span>, 849.98<\/span>);   \n<\/span><\/span>\n<\/span><\/span>INSERT<\/span> INTO<\/span> order_items_table (order_id, product_id, quantity, price) VALUES<\/span>\n<\/span><\/span>(1<\/span>, 1<\/span>, 1<\/span>, 999.99<\/span>),   \n<\/span><\/span>(2<\/span>, 2<\/span>, 1<\/span>, 699.99<\/span>),   \n<\/span><\/span>(2<\/span>, 3<\/span>, 2<\/span>, 299.98<\/span>); \n<\/span><\/span>\n<\/span><\/span>SELECT<\/span> * FROM<\/span> products_table;\n<\/span><\/span>SELECT<\/span> * FROM<\/span> customers_table;\n<\/span><\/span>SELECT<\/span> * FROM<\/span> orders_table;\n<\/span><\/span>SELECT<\/span> oi.order_id, p.name AS<\/span> product_name, oi.quantity, oi.price\n<\/span><\/span>FROM<\/span> order_items_table oi\n<\/span><\/span>JOIN<\/span> products_table p ON<\/span> oi.product_id = p.product_id;\n<\/span><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Auto
Auto Increment Example 4<\/figcaption><\/figure>\n\n\n\n
You can see the results are correct. <\/p>\n\n\n\n
Use Cases of Auto Increment<\/h2>\n\n\n\n