Mysql - Sequences
In databases, a sequence is a database object that generates a sequence of unique numerical values, typically used for creating unique identifiers for rows of a table. Sequences are often used in conjunction with primary keys to ensure that each row in a table has a unique identifier.
Key Features of Sequences
- Unique Values: Sequences generate unique values, ensuring no duplication.
- Incremental Values: Values generated by sequences can be incremented by a specified value.
- Customizable: You can specify the starting value, increment value, minimum and maximum values, and whether the sequence should cycle when it reaches the end.
- Automatic Generation: The next value in the sequence can be generated automatically when inserting a new row into a table.
Using AUTO_INCREMENT for Sequences
The `AUTO_INCREMENT` attribute allows you to automatically generate a unique value when a new row is inserted into a table. This is the closest feature to sequences in MySQL.
Creating a Table with AUTO_INCREMENT
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
PRIMARY KEY (id)
);
In this example:
- The `id` column is defined with the `AUTO_INCREMENT` attribute.
- Each time a new row is inserted into the `employees` table, MySQL will automatically generate a unique value for the `id` column.
Inserting Data into the Table
When you insert data into the table, you don't need to specify a value for the id column. MySQL will automatically generate the next available value.
INSERT INTO employees (name, department) VALUES ('Jane Smith', 'IT');
INSERT INTO employees (name, department) VALUES ('John Doe', 'HR');
Output will be the following -
| id | name | department |
|---|---|---|
| 1 | Jane Smith | IT |
| 2 | John Doe | HR |
Managing AUTO_INCREMENT Values
You can manage the `AUTO_INCREMENT` value in several ways:
- Setting the Initial Value: You can set the initial value of the `AUTO_INCREMENT` column when you create the table or after the table is created.
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
salary DECIMAL(10, 2),
PRIMARY KEY (id)
) AUTO_INCREMENT=1000;
This sets the initial `AUTO_INCREMENT` value to 1000.
- Altering the AUTO_INCREMENT Value: You can change the `AUTO_INCREMENT` value for an existing table.
ALTER TABLE employees AUTO_INCREMENT = 2000;
This changes the next `AUTO_INCREMENT` value to 2000.
Terms of Use About Us Privacy Policy
All Rights Reserved.