| |||
| |||
The one thing that primary, unique, and foreign keys all have in common is the fact that each type of key can consist ofmore than just one column from a given table. In other words, foreign, primary, and unique keys arenot restricted to having just one column from a given table – each type of key can cover multiple columns. So, that is one feature that all the different types of keys share – they can each be comprised of more than just one column, which is something that many people in software are not aware of.
Of course, the database programmer is the one who will actually define which columns are covered by a foreign, primary, or unique key. That is one similarity all those keys share, but there are also some major differences that exist between primary, unique, and foreign keys. We will go over those differences in this article. But first, we want to give a thorough explanation of why foreign keys are necessary in some situations.
Foreign keys are used to reference unique columns in another table. So, for example, a foreign key can be defined on one table A, and it can reference some unique column(s) in another table B. Why would you want a foreign key? Well, whenever it makes sense to have a relationship between columns in two different tables.
Suppose that we have an Employee table and an Employee Salary table. Also assume that every employee has a unique ID. The Employee table could be said to have the ‘master list’ of all Employee ID’s in the company. But, if we want to store employees salaries in another table, then do we want to recreate the entire master list of employee ID’s in the Employee Salary table as well? No – we don’t want to do that because it’s inefficient. It would make a lot more sense to just define a relationship between an Employee ID column in the Employee Salary table and the “master” Employee ID column in the Employee table – one where the Employee Salary table can just reference the employee ID in the Employee table. This way, whenever someone’s employee ID is updated in the Employee table, it will also automatically get updated in the Employee Salary table. Sounds good right? So now, nobody has to manually update the employee ID’s in the Employee Salary table every time the ID is update in the master list inside the Employee table. And, if an employee is removed from the Employee table, he/she will also automatically be removed (by the RDBMS) from the Employee Salary table – of course all of this behavior has to be defined by the database programmer, but hopefully you get the point.
Foreign keys have a lot to do with the concept of referential integrity. What we discussed in the previous paragraph are some of the principles behind referential integrity. You can and should read a more in depth article on that concept here:Referential integrity explained.
A table can have multiple unique and foreign keys. However, a table can have only one primary key.
Unique key columns are allowed to hold NULL values. The values in a primary key column, however, cannever be NULL.
Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference aunique key.
Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.
While unique and primary keys both enforce uniqueness on the column(s) of one table, foreign keys define a relationship between two tables. A foreign key identifies a column or group of columns in one (referencing) table that refers to a column or group of columns in another (referenced) table – in our example above, the Employee table is the referenced table and the Employee Salary table is the referencing table.
As we stated earlier, both unique and primary keys can be referenced by foreign keys.
Would you like to thankProgrammerInterview.com for being a helpful free resource?Then why not tell a friend about us, orsimply add a link to this page from your webpage using the HTML below.
Link to this page:
Please bookmark with social media, your votes are noticed and appreciated:
