What is the difference between primary keys and foreign keys? Here’s how our database looks with the primary key columns for each table highlighted: user_id can contain duplicate data, since the same customer can place more than one order, and product_sku can also contain duplicate data, since two different customers might order the same product. In fact, in this table order_no is the only column that could be used as the primary key. In this table, the primary key is order_no, a unique number that identifies each order individually. Just as your government ID identifies you uniquely even if other people share your name or live at your address, a table’s primary key column identifies each row uniquely even if some of the other values in the row are shared with other rows.Ī table’s primary key column thus must be unique, and it cannot be empty or NULL.Ĭonsider the orders table in our example database. In other words, a primary key is like a row’s ID number.
(Technically, a primary key can be made up of multiple columns, but for our purposes here let’s think of it as a single column). What is a primary key?Ī primary key is a column in a table that is used as a unique identifier for each row, and used in the table’s primary index. Now that we’ve got that database in mind, let’s return to talking about the differences between primary keys and foreign keys.
Note that foreign keys are not mandatory, and a table may have no foreign keys. Here’s an illustration of how a foreign key constraint works visually:Īlthough this is a simplified example, we can see how foreign key constraints help establish clear relationships between tables across a database, and promote consistency by making it impossible to (for example) add a row in an orders table with a user who doesn’t exist in the users table. Foreign keys also help end-users by preventing errors and improving the performance of any operation that’s pulling data from tables linked by indexed foreign keys. They allow developers to maintain referential integrity across their database. In other words: foreign keys put the “relational” in “relational database” – they help define the relationships between tables.
This way, each row in the orders table can be associated with a specific user from the users table - and no orders can enter the system unless they’re connected to a valid, existing user. We can make a specific column in the “child” table a foreign key that references a specific column in the “parent” table, and the database will enforce that rule automatically – rows will only be added or updated in the child table if the value in the foreign key column of the row being updated matches an existing value in the referenced column of the parent table.įor example, a table of customer orders might have a user column with a foreign key attribute that links it to the user_id column in a users table (see above). We can think about this like a “parent/child” relationship in programming. The existence of a foreign key column establishes a foreign key constraint – a database rule that ensures that a value can be added or updated in column_a only if the same value already exists in column_b. table_1.column_a) that are linked to a column in a different table ( table_2.column_b). Start Learning What is a foreign key? (TL DR)Ī foreign key is a column or columns in a database that (e.g. Learn the SQL you need (and none of what you don't) for building performant applications. ORMs are powerful tools, but they aren’t perfect.
0 kommentar(er)
