For example, you can have a table with employees, where each
employee references the employee that is his or her boss (in this scenario, the big boss??™s row would probably
reference itself).
A foreign key is a column or combination of columns used to enforce a link between data
in two tables (usually representing a one-to-many relationship). Foreign keys are used both as
a method of ensuring data integrity and to establish a relationship between tables.
To enforce database integrity, the foreign keys, like the other types of constraints, apply
certain restrictions. Unlike PRIMARY KEY and UNIQUE constraints that apply restrictions to a single
table, the FOREIGN KEY constraint applies restrictions on both the referencing and referenced
tables. For example, if you enforce the one-to-many relationship between the department and
category tables by using a FOREIGN KEY constraint, the database will include this relationship
as part of its integrity. It will not allow you to add a category to a nonexistent department, nor
will it allow you to delete a department if there are categories that belong to it.
There??™s good news and bad news about the FOREIGN KEY constraint and MySQL. The bad
news is that the default storage engine in most MySQL instances??”MyISAM??”doesn??™t support
enforcing FOREIGN KEY constraints. The alternative to MyISAM is the InnoDB storage engine,
but InnoDB tables don??™t support full-text searching, which will be needed when implementing
the search feature (in Chapter 8).
Pages:
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207