With an ID column, the department
table would look like Figure 4-8.
Figure 4-8. Adding an ID column as the primary key of department
The primary key column is named department_id. We??™ll use this naming convention for
primary key columns in all data tables we??™ll create. In this scenario, having departments with
the same name is now acceptable, because they would have different IDs. (To guard against
unique column values for columns that are not the primary key you??™d need to use the UNIQUE
constraint, which is discussed next.)
There are two main reasons it??™s better to create a separate numerical primary key column
than to use the name (or another existing column) as the primary key:
Performance: The database engine handles sorting and searching operations much faster
with numerical values than with strings. This becomes even more relevant in the context
of working with multiple related tables that need to be frequently joined (you??™ll learn more
about this in Chapter 5).
Department name changes: If you need to rely on the ID value being stable in time, creating
an artificial key solves the problem because it??™s unlikely you??™ll ever want to change the ID.
In Figure 4-8, the primary key is composed of a single column, but this is not a requirement.
If the primary key is set on more than one column, the group of primary key columns
(taken as a unit) is guaranteed to be unique, but the individual columns that form the primary
key can have repeating values in the table.
Pages:
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145