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


#1

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.