What is a foreign key? (with SQL examples)

The Nugget

A foreign key is a column or set of columns in one database table that uniquely identifies a row in another table, enforcing a parent/child relationship and ensuring referential integrity.
This key mechanism helps maintain data consistency and prevents errors in relational databases.

Make it stick

🔗 A foreign key connects two tables by referencing a unique column in the parent table.
📜 Foreign keys are used to prevent invalid entries by ensuring the referenced data exists.
🏷️ Each primary key is unique to its table, while a foreign key can have duplicate values referencing multiple rows.
⚙️ With ON DELETE CASCADE, if a parent row is deleted, corresponding rows in child tables are automatically deleted too.

A foreign key is a constraint that links rows from one table to another, ensuring that a value in the foreign key column exists in the referenced column of the parent table.
Foreign keys help in organizing data relationships and enforcing rules to maintain data integrity across tables, crucial for relational databases.

A foreign key constraint prevents the addition of rows in the child table if they do not correspond to an existing row in the parent table.
Examples include linking an orders table's user_id to a users table’s user_id, ensuring each order belongs to a valid user.

Primary keys are unique identifiers for rows within the same table; foreign keys refer to primary keys in different tables, creating relationships.
Each table can have one primary key, while multiple foreign keys can exist to point to relationships in other tables.

Data Integrity: Enforces rules that prevent invalid data entries.
Data Efficiency: Reduces the need to duplicate data, as relationships can be established without repetition.
Contextual Referencing: Facilitates clear connections between data points, improving data retrieval consistency.

"Foreign keys put the ‘relational’ in ‘relational database’ – they help define the relationships between tables."
"The existence of a foreign key column establishes a foreign key constraint."
"Foreign keys ensure that a value can be added or updated only if it already exists in the referenced column."
"A primary key is like a row’s ID number, uniquely identifying each row within a single table."
"When a row is deleted or a value is updated, the same operation can be specified to occur in linked tables through foreign key constraints."

This summary contains AI-generated information and may have important inaccuracies or omissions.