Redundant use of manual entry after using auto increment-CodePudding

Source: GoalKicker.com SQL Notes

I found so many redundant queries in the lecture notes. Like inserting id value manually after declaring id column to auto-increment itself in the DDL. Isn't it?

DDL:

CREATE TABLE Books 
(
    Id INT NOT NULL AUTO_INCREMENT,
    Title VARCHAR(50) NOT NULL,
    PRIMARY KEY(Id)
);

DML:

INSERT INTO Books (Id, Title)
VALUES
(1, 'The Catcher in the Rye'),
(2, 'Nine Stories'),
(3, 'Franny and Zooey'),
(4, 'The Great Gatsby'),
(5, 'Tender id the Night'),
(6, 'Pride and Prejudice),
(7, 'Professional ASP.NET 4.5 in C# and VB');

CodePudding user response：

Yes, this is redudant. We dont have to state the ID value, because it will be issued automatically. Actually, it is even a bad idea to state the value we want inserted, because the DBMS shall care about which ID to issue, which can be important in situations when different sessions enter data concurrently.

AUTO_INCREMENT is MySQL syntax. We can redudantly state the ID value, such as in

INSERT INTO Books (Id, Title) VALUES (3, 'The Catcher in the Rye');

The next ID automatically issued will then be a 4, as can be seen here: dbfiddle MySQL.

In PostgreSQL this used to be SERIAL instead of AUTO_INCREMENT. After above insert statement, the next automatically issued ID would still be the 1. This could lead to problems of course, as the next one would be the 2, then the 3, but the 3 would already be inserted manually. Please see: dbfiddle PostgreSQL SERIAL.

In Oracle as of version 12 and in PostgreSQL as of version 10 we can use GENERATED BY DEFAULT AS IDENTITY to get the same behavior as with PostgreSQL's SERIAL: dbfiddle Oracle BY DEFAULT, dbfiddle PostgreSQL BY DEFAULT. Or we can use GENERATED ALWAYS AS IDENTITY so as to forbid the user stating a value for the ID altogether: dbfiddle Oracle ALWAYS, dbfiddle PostgreSQL ALWAYS. This is my favorite. Auto-IDs should be issued automatically, not by the user. It is good that the DBMS is able to detect this and prevent potential problems thus.

CodePudding user response：

Yes, this is redundant (if not outright an error). Thorsten explained the workings behind automatically generated values in detail.

However, you found this code on a site with SQL tutorials. In the context of setting up test data for an exercise, it might make sense to provide pre-defined ID values so that each row has a well-known primary key. If there are some interactive questions this makes validating results (from an entered SQL query) a bit easier.

But I would consider the combination of an identity (or auto increment) column together with hard coded values really bad coding style (note this is my personal opinion). If I want to have pre-defined PK values, I would not define the column as an identity or auto increment column.

In MySQL you can get away with that, but not in other databases like Postgres, Oracle or SQL Server. There, you'd either get an error if the identity column doesn't allow providing a value or you have a sequence (=generator) that is out of sync with the table data (which is even worse).

If I stumbled over code like that, I would probably look for a different tutorial.