What is a Foreign Key Constraint? Understanding Primary & Foreign Keys

Summary

This video explains the role of foreign keys in relational databases, emphasizing how they create relationships between tables and ensure data integrity. Using a fictional bookstore database as an example, it demonstrates how foreign keys link tables through columns like `user_id` and `product_sku`. Foreign keys enforce rules that only valid data can be inserted, ensuring that references in one table correspond to existing entries in another. The video also covers the importance of primary keys and best practices for maintaining consistent, accurate data across tables.

Takeaways

• 😀 Foreign keys link tables together in a relational database, creating clear relationships between them.
• 😀 Foreign keys ensure data consistency and validity by enforcing rules that reject invalid data entries.
• 😀 A primary key is a unique identifier for each row in a table, like an ID number for a user or an order.
• 😀 Every table in a relational database must have a primary key to uniquely identify its rows.
• 😀 The primary keys in a database (e.g., user_id, order_no, product_sku) ensure that each entry can be uniquely identified.
• 😀 Foreign keys establish relationships between columns in different tables, ensuring the data is valid and exists in both tables.
• 😀 When a foreign key is used, it ensures that an order cannot be added unless the referenced user and book exist in their respective tables.
• 😀 Foreign keys reject insertions if they don't match the unique values in the referenced table, ensuring integrity.
• 😀 The column in the table being referenced must contain unique values when creating a foreign key relationship.
• 😀 It is best practice for foreign keys to reference primary keys in other tables, maintaining relational integrity.

Q & A

What is the primary purpose of foreign keys in a relational database?

Foreign keys help establish relationships between tables and ensure the consistency and validity of the data entered into a database.

What are primary keys in a relational database?

Primary keys are unique identifiers for each row in a table. They ensure that each row is uniquely identifiable, even if other columns contain duplicate data.

Can a table exist without a primary key in a relational database?

No, every table in a SQL database must have a primary key to uniquely identify each row in that table.

How does the primary key in one table relate to other tables in the database?

The primary key in one table can be referenced by foreign keys in other tables to establish relationships and ensure data integrity.

What is the relationship between the `orders` table and the `users` and `books` tables in the example database?

The `orders` table has foreign keys linking it to the `users` and `books` tables, using the `user_id` and `product_sku` as references, respectively.

Why are foreign keys necessary in a relational database?

Foreign keys are necessary to formalize relationships between tables, and they also enforce rules to ensure that data is valid, such as ensuring an order can only be created if the associated user and book exist.

What does adding a foreign key between two tables do?

Adding a foreign key creates a formal link between two tables and enforces rules that ensure the consistency of the data, like making sure that an order’s `user_id` corresponds to a valid user and that the `product_sku` corresponds to a valid book.

What happens if a value that does not exist in the referenced table is entered in the foreign key column?

The database will reject the insertion, ensuring that only valid data is entered, such as ensuring an order can only be added if the user and book exist in their respective tables.

Why must the column in the referenced table (for foreign keys) have unique values?

The column in the referenced table must have unique values because a foreign key must point to a specific row in the referenced table. If the values were not unique, it would be unclear which row the foreign key should link to.

Is it important to reference primary keys when creating foreign keys?

Yes, it is considered good practice to reference primary keys when creating foreign keys, as primary keys are unique and ensure the integrity of the relationship between the tables.