What is a Foreign Key?
Databases are composed of tables in which data, or information, is stored. A foreign key is a mechanism in a relational database that allows for the association of those tables, or for the tables to have a relationship with one another. There are unique characteristics that a foreign key must have. It must be a column or a group of columns in a table whose values establish a relationship with values in another table within the same database.
A foreign key guarantees that rows in one table correspond to rows in some other table, thereby establishing database-wide relationships, or references. The table containing the foreign key is the "child," and the other table is the "parent." It is possible for the value of a foreign key to be null or empty and for it to actually reference or relate to what is known as the primary key of the table in which it is found. This is known as self-referencing.
One must have a good understanding of what a primary key is before attempting to fully comprehend the concept behind foreign keys. For example, if there are two tables in a database storing information about published books for a website that sells books, one table might be the publishers table and the other the books table. The publishers table could consist of two columns, a primary key that is always unique for every record and a publisher name column. A minimum of three columns would make up the books table, holding the primary key or unique identifier for each book, the titles of books and a column via which a relationship would be established with the publishers table. That column would be the foreign key.
The primary key for the publishers table could be something like "pub_id" with values of: P01, P02, P03, etc. A relationship would be established with the books table if it contained a pub_id column with the same values. This would be the foreign key that would relate this child table back to its parent — the publishers table.
A foreign key can, however, have a different column name than the parent key that it references. Sound understanding of the workings of primary and foreign keys are essential in maintaining referential integrity. Very large databases that are composed of many tables or that involve a junction table present more than one type of relationship, which can complicate the task of working with foreign keys.
@Mammmood - I know that in Microsoft Access when you create relationships you can choose an option that will enforce referential integrity. This means that if you delete records from the parent table then the database will delete matching records from the child table as well.
From what I understand, this is an option you can turn on or off, but you should at least be aware of it. Otherwise, you will wind up with “orphan” records—child records for which there are no parent tables. Database integrity gets really mucky at that point.
Databases do enforce their primary key and foreign key constraints. If you want to delete tables in a database you usually issue a “drop” command. However, if there are relationships that exist between keys in the database you have to issue additional commands. I think in Oracle you say something like “Drop tablename cascade constraints” or something like that. I’m not sure how you would drop foreign key constraints in SQL Server.
Post your comments