FAQ - Mod 3

Welcome to the frequently asked questions page for Module 3x. As many common questions can come up on Piazza, some of those questions are consolidated here.

Primary Keys

Why am I getting an error when adding a foreign key?

Non-Unique Primary Key

This issue can be a result of a non-unique primary key. Take the courses table in this module, for example. Multiple courses can have the same course code if they are in different departments, so generating a primary key in reference to only course code is not enough. Think about what other fields to include to make a primary key truly unique, as you cannot have repeating primary keys.

Primary Key and Foreign Key Parameters

Another reason that adding a foreign key can result in an error is if it does not reference the entire primary key. For a foreign key to be properly generated, it needs to reference the entire primary key, even if that means including multiple parameters.

Type Mismatch

A third common reason that a foreign key cannot be properly generated is that the parameters included when generating the key are different types. For example, of one of the parameters is of type TINYINT and another is of type SMALLINT, the key cannot be generated properly. When generating the key, make sure the parameters are of matching type.

Modifying Tables

Why can't I modify my table?

You will run into an issue if you try to modify your table after adding a foreign key. Anything from adding to the table, to changing types, to modifying/adding charsets will not be possible once the foreign key has been added. If you do need to edit the table after having added a foreign key, you will need to drop the key before making edits to the table. The error will likely look something like this, especially if you are trying to add or delete elements:

Error adding entry: Cannot add or update a child row: a foreign key constraint fails