In SQL, is it good practice to always include an
id column in every table?
Most of the time, including a column to store unique
id values, typically
INTEGER values, for each row can be a good idea.
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.