7/20/2023 0 Comments Foreign key mysql workbench![]() ![]() You create a foreign key constraint to maintain referential integrity. Foreign Key ConstraintĪ foreign key constraint is a database object that assists in keeping your foreign key data consistent. Plus it also helps prevent inconsistent data turning up in our database. If we update a unit name, it wouldn't affect the Fruit table because the UnitId will remain the same. It also makes it easier if we ever decide to update a unit name (change "Kilograms" to "Kilos" for example). Doing this is more efficient than repeating those unit names over and over again for every record that's created in the Fruits table. While it is not necessarily "wrong" to do this, it is generally more efficient to store one record for each those unit names in a separate table, then reference that table via the UnitId column. We would also see "Bunch" repeated, and any other popular unit type. We would see "Kilogram" repeated over and over again against many records. Then we would end up with many records sharing the same value for the unit name column. Without using a foreign key relationship, we could just write the unit names straight into the Fruit table (and perhaps call the column "Unit", "UnitType" or "UnitName"). Seeing as many records in the Fruit table will share the same unit name (eg, "Kilograms", "Container", "Bunch", etc), we should think carefully before adding duplicates to our database. Reducing duplication is a key benefit of relational database management systems. The good thing about setting up the database in this way is that, we don't need to repeat "Kilograms" for every record that uses that unit. Therefore, we now know that apples are measured in kilograms. You can see that this record represents Kilogram. Now look at the Units table for the record that contains a 3 in the UnitId field. You can see that the Fruit.UnitId field contains a 3. So if our Fruit table contains a record like this: FruitIdĪnd our Units table contains the following records: UnitId the record that has the corresponding UnitId). This enables the Fruit.UnitId to reference the data in the other columns for that record (i.e. Therefore, the value that we insert into Fruit.UnitId must correspond to a value in Units.UnitId. The UnitId field on the Fruit table is a foreign key to the UnitId field on the Units table. The black line that links the two tables indicates a foreign key. ![]() Here's a diagram of our FruitShop database showing the relationship between the Fruit table and the Units table. This comes in handy when a column contains data that is represented in another table. ![]() This defines a relationship between the two tables.Ī foreign key allows you to cross-reference related data across tables. What is a Foreign Key?Ī foreign key is a column (or collection of columns) in one table that uniquely identifies a row of another table. Our database can also contain foreign keys. But primary keys aren't the only "key" type. So we've established that a primary key provides a unique identifier for the table. Foreign keys are an integral part of creating a relationship in relational databases. ![]()
0 Comments
Leave a Reply. |