Primary and Foreign Key Mistakes

Primary and foreign key columns are the most important columns in a table, so it is important to make sure they are defined properly.
Common mistakes associated with primary and foreign keys include:

1. Neglecting to identify a primary key
2. Storing meaningful information in a primary key column
3. Neglecting to identify foreign key columns

Primary keys uniquely identify each row in the table, and foreign keys link the table to related tables. Beyond including a primary key column in every table, which is mandatory, it is important to remember when creating a primary key that primary key columns should not contain meaningful information. It may be tempting to make an existing column, such as a phone number, do double duty, but two customers sharing a phone number invalidates the scheme. It is far safer to create a column that contains an arbitrary value for each new table row. It is also important to remember that a linking table (also known as an associative entity, which is the artificial table created to resolve a M:N relationship) must receive the primary from both of the tables it links together (thus forming a composite primary key).

When working with foreign keys, remember to make sure you identify the column as a foreign key when you create the table. If you forget to include the column, or if you neglect to identify the column as a foreign key when you create the table, you will need to modify the table to include that column, or, if necessary, re-create the table entirely.

• Foreign key does not have to be a Primary Key: A foreign key in a relational database does not have to be a primary key in the table it references. A foreign key is a column or a set of columns in one table that uniquely identifies a row in another table. It is used to create a link between the two tables. While a foreign key often references a primary key in the related table, it can also reference a unique key. The primary requirement is that the foreign key must match the unique constraints of the referenced column(s) to maintain referential integrity. This ensures that the value in the foreign key column(s) exists in the referenced column(s) of the other table.

Taken to extremes, too much normalization can lead to a database that scatters related data all over the place for little additional benefit. It can make the design confusing and can slow performance. When you normalize, think about what a change will cost and what benefits it will provide. Think about how the data will be accessed. If data is only read and written through stored procedures or middle-tier code, that code can help play a role in keeping the data consistent and may allow you to get away with slightly less normalization in the database's tables. Putting every table in Fifth Normal Form or Domain/Key Normal Form isn’t always necessary to keep the data safe.
I once worked on a project where a certain database developer (who coincidentally had just taken a class in database normalization) wanted to split every data value out into a separate table. For example, a customer record would contain little more than a CustomerId. Then a Values table would hold the actual data in its three fields Id, ValueName, and ValueData. To look up a customer's name, you would search the Values table for a record with Id equal to the customer's ID and ValueName equal to ‘‘Name.’’ In some bizarre otherworldly sense, this table is very normalized and it lets you do some amazing things. For example, you could decide to add a new EarSize field to the customer data without changing the tables at all. However, that design doesn’t reflect the structure of the data so it would be next to impossible to use.
The next lesson describes mistakes associated with relationships and referential integrity.

Before moving on to the next lesson, click the Quiz link below to reinforce your understanding of common database design mistakes.
Database Design Mistakes - Quiz