Is primary key Auto incremented by default?-CodePudding

I created a table with a primary key as INTEGER but did not specify it as auto increment. Still, when I add new rows to my database it auto increments the primary key. Am I missing out on something? If not, how can we make a primary key not auto incrementing?

I am using SQLite database

Code of:

CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO test (name) VALUES ('John');

INSERT INTO test (name) VALUES ('Mary');

Gives John id of 1 and Mary id of 2 i.e it autoincrements without having it specified for the id

CodePudding user response：

That's because in sqlite, using a column with the type INTEGER PRIMARY KEY creates an alias to rowid. This will automatically assign an id i.e rowid to the column on INSERT if no column values are provided.

If you explicitly specify INTEGER PRIMARY KEY AUTOINCREMENT as the type, it will change the default behaviour of using rowid. One change of this is that previously deleted rowids won't be re-used.

CodePudding user response：

Every brand of SQL database implements auto-incrementing primary keys differently. It's weird that there is so much variation.

In the case of SQLite, if you define the primary key column as an INTEGER, it is implicitly an auto-incrementing column.

It must be INTEGER, because this automatic behavior doesn't happen if the data type is simply INT.

Demo.

CodePudding user response：

D:\TEMP>sqlite3
-- Loading resources from C:\Users\Luuk/.sqliterc
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE test (id INT PRIMARY KEY, name TEXT);
sqlite> INSERT INTO test (name) VALUES ('John');
sqlite> INSERT INTO test (name) VALUES ('Mary');
sqlite>
sqlite> SELECT * FROM test;
id  name
--  ----
    John
    Mary
sqlite>

No, it does not do auto increment...

And, when using INTEGER:

D:\TEMP>sqlite3
-- Loading resources from C:\Users\Luuk/.sqliterc
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE test(id INTEGER primary key, name TEXT);
sqlite> INSERT INTO test(name) VALUES ('John');
sqlite> INSERT INTO test(name) VALUES ('Mary');
sqlite>
sqlite> SELECT * FROM test;
id  name
--  ----
1   John
2   Mary
sqlite>

Ah, now where are incrementing!

But is is all documented, see: https://www.sqlite.org/autoinc.html

CodePudding user response：

Auto-incrementing of a primary key is turned on by setting the IDENTITY property. For example:

CREATE TABLE [dbo].[Customers](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [varchar](100) NOT NULL,
    [PhoneNumber] [varchar](20) NOT NULL,
    [EmailAddress] [varchar](100) NOT NULL,
    [CreateDate] [datetime2](7) NOT NULL,
    [LastUpdated] [datetime2](7) NOT NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )
    WITH
    (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]