A foreign key is a field (or a set of fields) in a table that uniquely identifies a row of another table. The table in which the foreign key is defined is called the “child table” and it (often) refers to the primary key in the parent table.
Foreign key constraints can then be used to define how data integrity is enforced between two tables (e.g., when a table row is deleted or updated).
If done correctly, the link between the primary key and the foreign key will always be maintained, so your database will never be left with orphaned records in the child table (e.g., leftover contact information tied to a removed customer). The following simplified (and not fully-normalized) customer database example helps us visualize these foreign key concepts.
1. Defining Foreign Keys with CREATE TABLE
Go into the MySQL console by typing the mysql command with the correct user and password arguments. If needed, type “man mysql” to get more information.
Create a database and start using it:
Creating the Tables
Now create the two tables:
Verifying the Table Structure
Let’s see what our database structure looks like:
Note the MUL value in the Key column of the contact table. This tells us the customer_id field is the first column of a non-unique index (meaning it can have multiple rows with the same value).
Adding Data to the Table
Now we can fill our two database tables with some sample data. First, the customer table:
Our customer table now looks like this:
Some sample data for the contact table:
Our contact table now looks like this:
Now that we have the sample data, let’s see how foreign keys help preserve data integrity.
Deleting Referential Data (to See How Data Integrity is Preserved)
Earlier we defined the foreign key constraint as:
“FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE”
This means that when we delete a specific customer, the related contact table rows should also be deleted. Also, ON UPDATE CASCADE will cascade any updates on the parent table to referenced fields in the child table (in this case, customer_id).
Let’s first test deleting a customer:
When we look at our tables again, we see that both tables were changed as required, since all the contact rows for Skip were also removed:
Updating Referential Data (to See How Data Integrity is Preserved)
Now let’s update the customer_id of Elaine, to test the ON UPDATE CASCADE:
The needed changes have now been made to both tables:
2. Defining Foreign Keys with ALTER TABLE
It is also possible to add foreign keys to tables after they have already been created. Instead of doing the table creation in one step, as we did before, we can also do it in multiple steps.
Creating & Then Altering the Tables
Note that you can’t change an existing foreign key. You will first have to remove the existing foreign key by its symbol name and then you can define a new foreign key as we did previously.
Final Words on Foreign Keys
Foreign keys are integral to good database management. If you are now interested in looking at more advanced examples of their use, see the Examples of Foreign Key Clauses section here.
On that page is an example in which a “product_order” table has foreign keys for two other tables. One foreign key references a two-column index in the “Product” table. The other references a single-column index in the “Customer” table.
Other interesting foreign key examples can be found here.
Addendum: Foreign Key Constraints
Foreign key constraints are used to keep the data in your tables consistent when either removing (ON DELETE) or updating (ON UPDATE) table row data.
The syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:
The “CONSTRAINT [symbol]” part is optional and can be used to define a name for the foreign key. This is useful for removing foreign keys by their symbol name and also for producing clearer error logging.
The index_name value represents a foreign key ID and is ignored if there is already an explicitly defined index on the child table that can support the foreign key. One or more columns can be used to uniquely identify the foreign key.
Reference Option
Used by both ON DELETE and ON UPDATE, reference_option can point to one of the following:
CASCADE:
This command deletes or updates the row from the parent table, and automatically deletes or updates the matching rows in the child table.
RESTRICT:
This rejects the DELETE or UPDATE operations for the parent table. Specifying RESTRICT is the same as omitting the ON DELETE or ON UPDATE clause. In other words, rejection is the default action in MySQL.
NO ACTION:
A keyword from standard SQL, NO ACTION is essentially equivalent to the RESTRICT command in MySQL.
SET NULL:
Finally, the SET NULL command deletes or updates the row from the parent table, and sets the foreign key column (or columns) in the child table to NULL. This action can be useful in situations where the rows in the child table should not be deleted when the parent table row is removed.
0 comments:
Post a Comment