Is it good practice to include an id column in every table?

Question

In SQL, is it good practice to always include an id column in every table?

Answer

Most of the time, including a column to store unique id values, typically INTEGER values, for each row can be a good idea.

A unique id allows for more convenience, and one of the only real downsides is the extra memory required to store the additional values.

One benefit to having such a column is when joining multiple tables. When you are joining tables, as you will do later in the course, it is much quicker and easier to JOIN on a column of INTEGER values than on other value types like TEXT. When working with large amounts of data, the time saved can be much more evident and important.

In addition, another benefit to include an id is that its value is independent from the data in the row. This means that even if we update the values in other columns of a row, the id stays unchanged.

Furthermore, having unique id values prevents rows of data from being mixed up, especially for duplicate records. Without a unique identifier of the rows, it can get troublesome figuring out which row we want to use.

Most tables allow for an auto-incrementing identity value for each row added to the table, which you can specify when creating the table, or some will automatically include one.

18 Likes

i didn t understand the last one
Most tables allow for an auto-incrementing identity value for each row added to the table, which you can specify when creating the table, or some will automatically include one?
i didn t understand what we mean by this phrase ?

1 Like

It means that when you add a new row to the table, you will not have to set the value in the id column for the added row since most tables can do that for you.

6 Likes

You can utilize the SERIAL column data-type when creating a table with auto-incrementing identity
as the following:

-- Create a new table called rdbms
CREATE TABLE rdbms (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
website TEXT UNIQUE
);

-- Add some data to rdbms table
INSERT INTO rdbms (name, website)
VALUES
('SQLite', 'sqlite.org'),
('PostgreSQL', 'postgresql.org');

-- Review the table we just created
SELECT * FROM rdbms;

Notice when you run the query, the ‘id’ column has auto-incrementing integers.

8 Likes

If we do like what you did…we will get the ID values as NULL. Correct me if am missing something…

1 Like

You’ll get NULL in SQLite, but if you try to run it in your PostgreSQL (e.g. in Postbird), id is added and incremented automatically as stated by Zack.

1 Like