Difference between primary key and foreign key
March 27, 2013 1:14pm CST
Dear All, my data base exam this weekend Can anybody explain me the difference between foreign key , primary key and composite key with example? explain relationships One-to-One (1:1) relationships One-To-Many (1:n) relationships Many-To-Many (n:m) relationships
1 person likes this
27 Mar 13
Here I'll explain all these with a few examples: 1. Primary Key: Lets take an example of a class, there could be more than one student with the same name, however, no one can have the same roll number in a class. That's why you can identify a student with his/her roll number because that's unique and a must have attribute of the student. If you have to create a student table that will have his/her, name, subjects, age, roll number etc stored. In that case, every row of that table can only be uniquely identified by checking the value of the roll number column. This type of column in any table is called Primary key. Because, this will help you to identify any row in the table. 2. Foreign Key: Lets take an example of an engineer who is working in a foreign country and his family lives in India and he's the only earning member in his family. If you have to get the total earning of that family, you can get it by checking the database of the company on the basis of the employee id of this foreign family member of this family. Explanation: Let's say we've two tables: 1. Family table with member name, employed, age, gender, address, employee id etc 2. Employee table with employee name, employee id, age, gender, emp_address etc. Now if you see here, there is a common column in these two tables, it's employee id. The employee_id can be NULL in the Family table for non-employed members, that's why it can't be the primary key here, however, in one family you will always have unique names of the members and that's why it'll be the primary key. (Read the definition of Primary key) However, the Employee ID column in the employee table will certainly be the primary key. To get the earning of the family, we need to have the employee id as a bridge so that we can link the two tables and fetch the data from both the tables. Therefore in output we can have the columns from both the tables and it's possible only because of the employee id table. Employee_ID column in Family table is a foreign key. (Read Definition of foreign key)
27 Mar 13
Forgot to mention about composite key. Lets make a few changes in the foreign key example, lets say we don't have the employee_Id column in either tables. In that case, we have a primary key in our family table. In company table, we can't have only one table as primary table, however, we can logically group two columns to get a primary key, in our case, it'll be the employee name and the address. This logical grouping of more than one column in any table to get a primary key like behavior is called Composite key. (Read the definition of the Composite key) Please refer to this link, it explains relationships in detail: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi41.htm Do let me know if you have any doubts on this. You can send me PM. All the best for your exams, try not to spend more time on myLot before exams!
28 Mar 13
A primary key is a key that "resides" in the entity itself, a foreign key is the primary key of another entity that is placed in this other entity -kind of referenced in it, indicating a relationship. Example of a 1:1 is a man can only have 1 wife and a woman can only have 1 husband. (1:n), a mother can have many children, while a child can only have one mother...Hope it helps.