Primary keys, foreign keys and relationships between tables are not always easy to learn when you are starting out.
Besides this, there is cardinality, modeling, queries…
Of course all this can be very confusing. I know, because I have been there, and I understand you.
Thinking of solving this cruel doubt, in this post I will show you the main differences between them and how you should use them in your tables.
First of all, the Primary Key is used when we need the following objectives in a table:
- To have uniqueness of a record
- That this record is NOT null
- Or that this record can identify the table
Therefore, the Primary Key identifies a table by the uniqueness of a record and by its requirement that the record must NOT be null.
However, a table can have only one Primary Key, which is usually the ID or COD or something like that…
NOTE: A primary key can be created from two other keys, thus generating a composite key. For example: by joining the fields ‘name’ and ‘last-name’, we have a unique ID formed by two fields (composite key).
The Foreign Key is, above all, synonymous with the relationship between tables. Why is this so?
Because a Foreign Key is nothing more than the Primary Key of a table ‘placed’ in another table. And why is this done?
In short, the Foreign Key, besides connecting tables, has these other purposes:
- It prevents you from adding an invalid value to a table’s ID
- It prevents you from deleting a record if it is referenced in another table
In brief: the Foreign Key won’t let you add a value that doesn’t exist to the Primary Key field when you reference another table to the same field.
It will also not let you delete data from your table, without first deleting it from the original table, which you reference using the Foreign Key.
However, unlike the Primary Key, a table can have more than one Foreign Key.
Main Difference Between Primary and Foreign Key
To conclude, suffice it to say that the Primary Key identifies and guarantees uniqueness to a record in a table. Thus it is unique, but also not null, i.e. it is mandatory to have a valid value there.
The Foreign Key, on the other hand, promotes the relationship between tables. A Foreign Key is nothing more than the Primary Key of another table.
Therefore, when you need the union between two or more tables, it is the one that makes it possible to query and manipulate the records.
Primary Key: identification
Foreign Key: relationship
Subscribe to the blog to receive more content like this one.