Thursday, September 6, 2012

Difference between Primary Key and Foreign Key

primary key is a column which uniquely identifies the records in a table. In a broad sense, a primary key is the mixture of a unique key and an index: A collumn with a primary key is indexed to deliver a faster query, and doesn't allow duplicate values to ensure specific data.
Most programmers recommend all tables having a primary key (and only one) to enhance the speed of queries and overall database performance.
An example of a primary key may be found in a table named "departments," which might have a collumn named "department_number" that uniquely identifies each department in the table with a number.
foreign key is a column (the child column) in a table which has a corresponding relationship and a dependency on another column (the parent collumn) that is usually in a different table. Parent columns can have multiple child collumns, but a child collumn can only have one parent column.
The child collumn is the collumn with the foreign key; the parent collumn does not have the foreign key "set" on it, but most databases require the parent collumn to be indexed.
Foreign keys are made to link data across multiple tables. A child collumn cannot have a record that its parent collumn does not have.
Say a table named "employees" has 20 employees (rows) in it. There are 4 departments in the "departments" table. All 20 employees must belong to a department, so a collumn in the "employees" table named "department" would point to the primary key in the "departments" table using a foreign key. Now all employees must belong to a department as specified by the "departments" table. If a department isn't specified in the "departments" table, the employee cannot be assigned to it.

No comments:

Post a Comment