In any database, data held in one table can be related to data held in another table. For the database to operate effectively, the relationship between the different tables needs to be clearly defined. This is done through the use of primary and foreign keys. In this post, we’ll explain the purpose and use of primary and foreign keys in a database.
Essentially, primary and foreign keys are used as a way to constrain or link related data in a database. This ensures that data remains consistent and that the database contains no redundant data. For example, if you delete a table (or even a row in a table) that other tables rely on, the redundant data is removed. This helps prevent data integrity issues which can cause problems with applications that make use of such data.
What is a primary key used for?
A primary key is essential if a table is to qualify as a relational table. It is made up of one or more columns whose data uniquely identifies each row in the table. For example, if the data in the rows contained house numbers, the primary key could be used to identify the streets on which the houses were situated. To qualify as a primary key, it is the data within each column that must be unique, not simply the column names themselves. At the same time, no value in the columns can be blank or NULL.
A primary key, therefore, is used to ensure the data in a specific column is unique. If you wish to set constraints with a primary key, you can only do so by setting a foreign key to another column. This creates a relationship with the column that has the primary key set.
One of the main uses of a primary key is to create a users table. The id column is likely to be a primary key because it needs to be unique. You may also set this column to auto–increment, but the most important component is the primary key.
Why do I need to use a primary key?
Even if a column set as a foreign key has duplicate data in multiple rows, it still relies on the unique information in the primary key. If there were non-unique rows in the primary key column, there would not be a use for a foreign key. This is because you would be mapping one row in the foreign key to two rows in the primary key.
In essence, primary keys ensure that:
- Data inserted in a table with a foreign key can only be data that uniquely exists in the primary key column.
- If a deletion or update occurs, the action you specified will be undertaken to ensure data integrity and prevent data redundancy in your database. Two primary actions are cascade and restrict. A cascade will delete or update the row from the parent table and automatically delete or update the matching rows in the child table. A restrict, meanwhile, rejects the delete or update operation for the parent table.
What’s a foreign key used for?
Foreign keys ensure that data stays integral, consistent and updated. While a primary key makes sure that data is unique in one column, the foreign key constraints enforce referential integrity by preventing values that aren’t found in the related table’s primary key being entered.
In most database-driven applications, you will need multiple tables. This is because different areas of your application will reference ids or unique data in another table. With a profile comments table, for example, your application will need to understand which user id the profile comment is for.
Here, you can use a foreign key which maps to the primary key, ensuring data remains consistent and enabling you to set the foreign key to cascade. This makes sure that any DELETE and UPDATE actions will be cascaded down to any affected row (such as the foreign key).
Conclusion
Hopefully, this post will have helped you understand the purpose and uses of primary and foreign keys in databases.
If you need more answers to your web hosting questions, visit our blog or for more technical information, take a look at our knowledgebase.
