Does the primary key get automatically assigned when inserting rows into a table?

Hey, so I’m a bit of a noob obviously, but there’s something that’s re-occurred across multiple exercises that’s been bothering me. (I’m doing the create a back end web app with JavaScript skill path.)
When inserting a row into a table, the primary key (id usually) is often never assigned! Is SQL built to assign a value to that automatically?
For example:

INSERT INTO Artist (name, date_of_birth, biography, is_currently_employed) VALUES ($name, $date_of_birth, $biography, $is_currently_employed)

when the table schema is

(id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, date_of_birth TEXT NOT NULL, biography TEXT NOT NULL, is_currently_employed INTEGER NOT NULL DEFAULT 1)

(the ID was never assigned in the INSERT INTO statement, yet it is the primary key)
Would appreciate an answer as it would help my understanding of this language very well. Thanks in advance!

1 Like

Hello @bit3773680424 ,

I can say that in Oracle RDBMS you can automatically assign an id by setting up a sequence. Every time, you add a row to the table you would reference the sequence value. This would automatically increment every time you used it. I would imagine there would be a similar feature in other databases.

A quick search on CREATE SEQUENCE + dbms would give you some more details.

** Sequences and Auto-Incrementing (MySQL & mSQL)

3 Likes

Ah, so the same feature would likely be implemented in the sqlite3 JS library. Thanks! This helps!

Hey, thanks for the reply. I guess SQLite doesn’t have that type of feature.

You would have to do it manually sql - How can I use sequences in SQLite? - Stack Overflow. Hence the ‘lite’.

Hello,

As the column id in an INTEGER and a PRIMARY KEY, the value will be filled automatically with an unused integer (which will usually be one more than the largest existing ID).

You can find more information about SQLite Autoincrement and about the table creation (3.5 is about the PRIMARY KEY)

2 Likes

hmm, but in the project I’m currently on and previous projects, I’m never asked to specify a primary key when inserting the row, but I will sometimes retrieve the primary key from said row afterwards. Makes me wonder how it gets there.

ohhh this makes a lot of sense now, thank you.
From this article, I take it it isn’t good practice to let it be auto defined?

Using AUTOINCREMENT is to avoid if not needed, yes. But setting an identifier in a table (with the INTEGER PRIMARY KEY and without AUTOINCREMENT) is a good practice to have.

Indeed, the ID will be useful when you want to get a specific record in a table, and it’s the fastest way to get it.

The AUTOINCREMENT needs to be avoided due to the algorithm used to manage the incrementation. For the AUTOINCREMENT, the algorithm needs to store the latest value used to ensure that it will always assign a new value and not reuse a value already assigned in the past.
Simply put, the INTEGER PRIMARY KEY “just” check the largest existing ID in the table and add one to it to get the ID to assign to the new row. This means that if the record with the largest ID has been deleted, you may have a new record with an ID of an old record.

I hope my explanations are clear enough :slight_smile:

2 Likes

I hope my explanations are clear enough

This answered my question exactly, thank you so much! :grinning:

1 Like