17:23

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Adding rows to a table with identity column page 4

How to reset auto increment values to avoid gaps (MySQL)?

Let's take the table considered on the previous page and insert into it 3 rows.

CREATE TABLE test_Identity (
     id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY  (id)
    );
    INSERT INTO test_Identity VALUES(),(),();
    SELECT * FROM test_Identity;

id
1
2
3

If we shall delete the last row, numbering will proceed not with 3, but with 4. I.e. last value of the counter is kept and used at the subsequent addition of rows:

DELETE FROM test_Identity WHERE id=3;
INSERT INTO test_Identity VALUES();
SELECT * FROM test_Identity;

id
1
2
4

There is a question: " Whether is it possible to make numbering proceeding from last available value? "
Apart from a question about what it is necessary for, the answer is - possible. But this value of the counter should be set manually. So,

DELETE FROM test_Identity WHERE id=4;
ALTER TABLE test_Identity AUTO_INCREMENT = 3;
INSERT INTO test_Identity VALUES(),(),();
SELECT * FROM test_Identity;

id
1
2
3
4
5

Pages

INSERT IDENTITY autoincrementable column

Previous [INSERT statement]

[Table-values constructor] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Data modification statements

INSERT statement

Adding rows to a table with identity column page 4

How to reset auto increment values to avoid gaps (MySQL)?