You are currently viewing SQL 1.52 SQL AUTO INCREMENT

SQL 1.52 SQL AUTO INCREMENT

SQL AUTO INCREMENT


In SQL, the AUTO_INCREMENT attribute is used to generate a unique numerical value when a new record is inserted into a table. It is particularly useful for generating primary key values automatically.

Step-by-Step Instructions

1. Creating a Table with AUTO_INCREMENT:

  • Let’s start by creating a simple table with an AUTO_INCREMENT column. We’ll name the table “employees” and give it three columns: “id”, “name”, and “salary”.
   CREATE TABLE employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(50),
       salary DECIMAL(10, 2)
   );

Explanation:

  • The id column is specified as INT and set as AUTO_INCREMENT. This means that every time a new record is inserted into the table, the id column will automatically increment by 1.
  • The PRIMARY KEY constraint is applied to the id column to ensure its uniqueness.

2. Inserting Records:

  • Now, let’s insert some records into the “employees” table.
   INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00);
   INSERT INTO employees (name, salary) VALUES ('Jane Smith', 60000.00);

Explanation:

  • When inserting records into a table with an AUTO_INCREMENT column, you don’t need to provide a value for the id column. The database will automatically generate a unique value for it.

3. Viewing Records:

  • Let’s retrieve the records from the “employees” table to see the generated id values.
   SELECT * FROM employees;

Sample Output:

   +----+------------+----------+
   | id | name       | salary   |
   +----+------------+----------+
   | 1  | John Doe   | 50000.00 |
   | 2  | Jane Smith | 60000.00 |
   +----+------------+----------+

Explanation:

  • As you can see, the id column has been automatically incremented for each inserted record.

4. Updating Records:

  • You cannot manually update an AUTO_INCREMENT column’s value. If you need to update the value of another column, you can do so using a regular UPDATE statement.
   UPDATE employees SET salary = 55000.00 WHERE name = 'John Doe';

Explanation:

  • In this example, we’re updating John Doe’s salary to 55000.00. Notice that we don’t specify the id column in the UPDATE statement.

5. Deleting Records:

  • When deleting records from a table with an AUTO_INCREMENT column, the value of the id column will not be reused.
   DELETE FROM employees WHERE name = 'Jane Smith';

Explanation:

  • Even though we’ve deleted Jane Smith’s record, the next inserted record will still have an id value of 3, not 2.

Conclusion


In this tutorial, you’ve learned how to use the AUTO_INCREMENT attribute in SQL to automatically generate unique numerical values for a column. This feature is commonly used for primary keys in database tables, ensuring each record has a unique identifier.

Leave a Reply