SQL auto-increment is a specific technique to create some unique numbers as a value to be stored in the tables. Database records, as a result, need specific primary keys while adding new rows to the database. It can also be done without any manual override with the help of the auto-increment feature. This article gives a detailed definition of auto-increment or key uniqueness. To keep it simple, we will begin with the intro and some practical implementation.
How Does Auto Increment Work?
When а column is set up as auto incrementable, the database system itself generates and provides sequential integer value to such column every time the new record is inserted. This sequential integer usually starts from an initial value which is specified and further generates, each new addition being produced by the sum of the successive previous terms. We all know auto-incremental columns are used as primary keys which carry along with the uniqueness of each record. Streamline database management by acting on data structures setup due to developers not has to manually make primary key values, making the disputes or errors diminish.
Also Read: Reset Auto Increment In MySQL
Examples of Auto Increment in SQL
Example 1:
In this implementation, we are using an SQL server. The auto-increment can be done using the IDENTITY keyword. There are two attributes for the IDENTITY keyword. The first integer will tell the initial value of the unique ID and the second will tell the incrementation steps.
In this example, we are starting the primary key with 1 and taking 1 step forward.
CREATE TABLE Table2 (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO Table2 (name) VALUES ('X'), ('Y'), ('Z');
SELECT * FROM Table2;
Code language: SQL (Structured Query Language) (sql)
In the results, you can see the unique primary keys are assigned to each row.
Example 2:
CREATE TABLE Table1 (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO Table1 (name) VALUES ('A'), ('B'), ('C');
SELECT * FROM Table1;
Code language: SQL (Structured Query Language) (sql)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.
In the result, you can see the unique ID is assigned to each name.
Example 3:
In PostgreSQL, the SERIAL is used to increment the primary key in the table. Let’s implement the query and verify the results.
CREATE TABLE Table3 (
id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO Table3 (name) VALUES ('P'), ('Q'), ('R');
SELECT * FROM Table3;
Code language: SQL (Structured Query Language) (sql)
You can see the outcome is correct.
Example 4:
In this example, we have created four different tables in our database to manage the E-commerce platform. This example implements the technique of auto-incrementation to generate unique primary integer values in each table of every entry.
The ‘AUTO_INCREMENT’ keyword is used in this query. Let’s see the final result to validate the query.
CREATE TABLE products_table (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
price DECIMAL(10, 2),
description TEXT
);
CREATE TABLE customers_table (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders_table (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers_table(customer_id)
);
CREATE TABLE order_items_table (
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES orders_table(order_id),
FOREIGN KEY (product_id) REFERENCES products_table(product_id)
);
INSERT INTO products_table (name, price, description) VALUES
('Laptop', 999.99, 'High-performance laptop with SSD storage'),
('Smartphone', 699.99, 'Latest smartphone with OLED display'),
('Headphones', 149.99, 'Wireless noise-canceling headphones');
INSERT INTO customers_table (name, email) VALUES
('A', 'A@example.com'),
('B', 'B@example.com');
INSERT INTO orders_table (customer_id, total_amount) VALUES
(1, 1099.98),
(2, 849.98);
INSERT INTO order_items_table (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 999.99),
(2, 2, 1, 699.99),
(2, 3, 2, 299.98);
SELECT * FROM products_table;
SELECT * FROM customers_table;
SELECT * FROM orders_table;
SELECT oi.order_id, p.name AS product_name, oi.quantity, oi.price
FROM order_items_table oi
JOIN products_table p ON oi.product_id = p.product_id;
Code language: SQL (Structured Query Language) (sql)
You can see the results are correct.
Use Cases of Auto Increment
- Database Management Systems: With auto-incrementing the primary key in the relational database system ensures data integrity and easy query building. They ensure that each entity in a table eigenvalues say, the owner, has a unique identifier, which makes the process of looking alike, changing a record, or deleting a record simple.
- User Authentication and Authorization: In websites and systems of web applications, that are attributed to user authentication, the most widespread keys are auto incremental primary keys which are unique to user accounts. Every user is given a certain unique ID, the significance of which lies in managing user data as well as user permissions and their control.
- E-commerce Platforms: It is necessary to have automatically generated incremented primary keys for the sake of managing product catalogues, orders, and customer data, due to the operating principles of e-commerce platforms. Unlike other departmental stores, online shopping sites do not have barcodes for every product, order and record. All transactions, inventories and customer service are done through a unique identifier.
- Content Management Systems: CMS platforms primarily utilize an auto-incrementing primary key to manage content units including blogs, articles, and multimedia such as images and audio-video clips. In this case, every content object will be tagged with a private identifier and this will enable a more proper organization, which will also bring ease when content is retrieved and published.
Summary
In this article, we have discussed the concept of Auto incrementation and also implemented some basic and complex examples. Auto incrementation is a process of generating unique IDs for databases. The use cases of auto incrementation are vast. These use cases are also explained in detail. Hope you will enjoy this article.