How to Insert Records with Auto-Increment in SQL

Insert with Auto-Increment

Write a SQL query to insert a record into a table where the primary key is auto-generated.

Solution:

-- Insert a record where EmployeeID is auto-generated.
INSERT INTO Employees (Name, Age, Salary) -- Exclude the auto-increment column.
VALUES ('Eve White', 27, 48000); -- Provide values for other columns.

Explanation:

1. Purpose of the Query :

1. The goal is to insert a new record into the Employees table while allowing the database to automatically generate the value for the EmployeeID column.
2. This demonstrates how to handle tables with an auto-incrementing primary key.

2. Key Components :

1. INSERT INTO Employees (Name, Age, Salary) : Specifies the columns for which values will be explicitly provided, excluding the auto-increment column (EmployeeID).
2. VALUES ('Eve White', 27, 48000) : Provides values for the specified columns (Name, Age, and Salary).
3. The EmployeeID column is omitted because it is auto-generated by the database.

3. Why Use Auto-Increment? :

1. Auto-increment ensures that each record has a unique identifier without requiring manual input.
2. This is particularly useful for primary keys, as it eliminates the risk of duplicate or invalid values.

4. Real-World Application :

1. For example, in a company database, you might use this query to add a new employee (Eve White) without manually assigning an EmployeeID, as the database automatically generates it.

Additional Notes:

• Auto-increment is typically used for primary key columns to ensure uniqueness and simplify data management.
• Scenarios where auto-increment is beneficial, such as:
• Automatically generating unique IDs for users, products, or orders.
• Reducing the need for manual ID assignment, which can lead to errors.
• Important Considerations :
• Ensure that the EmployeeID column is defined as an auto-incrementing primary key in the table schema.
• Verify that all required columns (other than the auto-increment column) are included in the INSERT INTO statement.

For more Practice: Solve these Related Problems:

• Write a SQL query to remove a foreign key constraint from a table using the ALTER TABLE and DROP CONSTRAINT commands.
• Write a SQL query to remove a check constraint from a table, ensuring that the command targets the correct constraint name.
• Write a SQL query to drop a unique constraint from an existing table, handling cases where the constraint name might be system-generated.
• Write a SQL query to remove a primary key constraint from a table, and then add a new composite primary key in its place.

Go to:

• SQL Data Manipulation Language (DML) Exercises Home ↩
• SQL Exercises Home ↩

PREV : Delete All Records.
NEXT : Update and Reset a Column.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.