We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
A column in one table that references the primary key of another table, enforcing referential integrity between related tables.
In short
A foreign key is a column (or set of columns) in one database table whose values must match a primary key value in another table, which links the two tables together and stops you from inserting rows that point to a parent record that does not exist.
In short
A foreign key is a column (or set of columns) in one database table whose values must match a primary key value in another table, which links the two tables together and stops you from inserting rows that point to a parent record that does not exist.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Foreign Key across a run of lessons, not one page. These 4 alone are about 165 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Deleting 507,591 rows changed the table size by 0.0 MB, and VACUUM changed it by nothing either. An unindexed foreign key made 100 parent deletes take 1,011 ms against 8.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
Three triggers created in one order fired in another, three rows were inserted and two exist, and a TRUNCATE emptied an audited table without firing the audit trigger once. None of it raised an error.
Set up an active-passive PostgreSQL database: a primary and a streaming standby, slots, synchronous_commit, hot standby and fencing, run in a real lab.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Hard Deletepay once,
yours for life
A foreign key is a rule attached to a column. It says: every value in this column must already exist as a primary key (or unique key) in some other table. The table holding the foreign key is the child; the table it points to is the parent.
Take an online store. You have a customers table with a primary key id, and an orders table with a column customer_id. If you declare customer_id as a foreign key referencing customers.id, the database guarantees that every order belongs to a real customer. You cannot insert an order for customer 9999 if no customer 9999 exists.
This guarantee is called referential integrity. Without it, you get orphan rows: orders that reference customers who were never created or were deleted, invoices tied to missing accounts, comments attached to deleted posts. Foreign keys push that consistency check down into the database engine so every application talking to the database gets the same protection automatically.
When you define a foreign key, the database registers a constraint. On every INSERT or UPDATE to the child table, the engine checks that the referenced value exists in the parent. On every DELETE or UPDATE to the parent, it checks whether any child rows still point to the row you are touching.
To make these checks fast, the parent side relies on the index that already backs its primary key. On the child side you usually want your own index on the foreign key column, because the engine looks up child rows during parent deletes and joins. Postgres, for example, does not auto-create that child index, so forgetting it can make deletes on big parent tables painfully slow.
You also choose what happens when a parent row is removed. ON DELETE RESTRICT, or NO ACTION, which is the actual default and behaves almost the same, blocks the delete if children exist. ON DELETE CASCADE deletes the children too. ON DELETE SET NULL clears the child reference. So deleting a customer can either be refused, wipe their orders, or detach the orders, depending on the rule you picked.
Use foreign keys whenever a row in one table is meaningfully owned by or attached to a row in another, and you want the database to never let those links break. They are the cheapest insurance against data corruption and they document your schema: anyone reading the table definition sees exactly how tables relate.
The cost is write throughput. Every insert and delete pays for the integrity check and the index lookups, so very high-volume write paths sometimes drop foreign keys and validate references in application code instead. CASCADE rules can also cause surprises, like one delete quietly removing thousands of rows across several tables.
Foreign keys also complicate sharding. Once a parent and child can live on different database servers, the engine can no longer enforce the constraint across the network, which is why many large systems and most NoSQL stores skip foreign keys and accept eventual cleanup. For a normal single-instance relational database, the safe default is to keep them on.
Picture a blog with a posts table and a comments table. comments.post_id is a foreign key referencing posts.id, declared with ON DELETE CASCADE.
A user writes a post (id 42) and ten comments arrive, each row storing post_id = 42. Because of the constraint, nobody can insert a comment with post_id = 999 when no post 999 exists, so you never end up with comments floating in space.
When the author deletes post 42, the cascade rule fires and the database removes all ten comments in the same transaction. You did not write any cleanup code; the foreign key handled it. If you had used ON DELETE RESTRICT instead, the database would have refused to delete the post until you removed the comments yourself, which is the safer choice when accidental deletes are expensive.
PostgreSQL
Enforces foreign keys with configurable ON DELETE and ON UPDATE actions, but leaves it to you to index the child column.
MySQL (InnoDB)
The InnoDB engine supports foreign keys and auto-creates an index on the referencing column; the older MyISAM engine ignored them entirely.
MongoDB
Does not enforce foreign keys; you either embed related data in one document or store a reference and check it in application code.
Amazon DynamoDB
Has no foreign key concept at all, since cross-item integrity does not survive its partition-based scaling, so apps validate relationships themselves.
A primary key uniquely identifies each row within its own table and cannot be null or duplicated. A foreign key lives in a different table and points at a primary key, linking the two; its values can repeat and, depending on the column, can be null.
Yes, unless the column is also marked NOT NULL. A null foreign key means no relationship for that row, for example an employee with no assigned manager yet. If the value is not null, it must match an existing parent key.
Yes. An orders table can reference customers, products, and shipping_addresses all at once, each through its own foreign key column. A foreign key can also span multiple columns when the parent key is composite.
It tells the database that when a parent row is deleted, all child rows pointing to it should be deleted automatically in the same transaction. It is convenient but dangerous, since one delete can wipe out large amounts of related data.
Two reasons. They add a check on every write, which costs throughput at high volume, and they cannot be enforced across multiple shards or servers. So heavily sharded relational setups and NoSQL stores often drop them and validate references in application code.
Primary Key
A column (or combination of columns) that uniquely identifies each row in a database table. Must be unique and not null.
Normalization
Organizing database tables to reduce redundancy by splitting data into related tables connected by foreign keys. Follows normal forms (1NF, 2NF, 3NF).
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
This page explains the idea. The course covers it step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
See the System Design Masterclass coursecourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Foreign Key in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
