In databases what is the logic difference between a primary key, foreign key, candidate key?

Question

Please put this in rich dummy terms, as easy for me to understand as possible, thank you!!

Answer 1

Candidate Key: any group of columns that can be used to uniquely identify a record (row) in the database. In other words, if I was on the phone with you, and I gave you the values of columns A, B, and C for a particular row, would you know exactly which row I was looking at, regardless of what row I chose? If so, ABC forms a candidate key. If there is any row that could not be uniquely identified using the values of ABC, then ABC is not a candidate key.



Primary Key: any candidate key which is chosen to be the primary key. You basically just pick a candidate key to be the primary key.



Foreign Key: this is a group of columns who form a candidate key for a row in ANOTHER table.



Example: imagine you worked at a school, and wanted to store information about students and their parents. You might have one table that stores info about STUDENTs, and another table for the PARENTs. Each record in the STUDENT table would need to have a set of columns (or maybe just one column), that identifies which row in the PARENT table corresponds to that student. A typical way to do this would be to assign each parent an ID number, and then store the ID number of the parent as a column in the student table. This parent ID number is a candidate key in the PARENT table (maybe even the primary key), and a foreign key in the STUDENT table.

Answer 2

First, a superkey is a set of columns within a table whose values can be used to uniquely identify a row.



A Candidate Key is a minimal set of columns necessary to identify a row, this is also called a minimal superkey.



A Primary Key is the candidate key that is used to uniquely identify each row in a table (i.e. the candidate that succeeded in your choosing).



A Foreign Key is a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.





e.g. I have a table called VITALS.



It has three columns: NAME, BIRTHDAY and STATUS.

There are two rows containing values:

Bob ---- 27 Dec ---- Alive

Alice ---- 30 June ---- Alive



Superkeys therefore will be (NAME,BIRTHDAY,STATUS), (NAME,BIRTHDAY) (NAME,STATUS), (BIRTHDAY,STATUS), (NAME) and (BIRTHDAY) since, if you use each of these sets of columns to look up any row, you will get a unique answer (note: only STATUS on its own is not a superkey because it contains the same value in both rows).



Candidate keys for this table will be the set of superkeys that are minimal i.e. have the least numbers of attributes (columns).

I have 6 superkeys:

1 containing 3 attributes = (NAME,BIRTHDAY,STATUS),

3 containing 2 attributes = (NAME,BIRTHDAY), (NAME,STATUS) & (BIRTHDAY,STATUS)

and 2 containing 1 attribute = (NAME) and (BIRTHDAY).

Candidate keys are therefore the 2 superkeys containing just 1 attribute, being NAME and BIRTHDAY columns on their own.



Primary key then is whichever of the Candidate keys I decide to use for uniquely identifying rows. I think NAME is most appropriate so that will be the Primary key.



Now, Foreign Key:



I have another table called EMPLOYEES. It has two columns named ID and SALARY. There are two rows containing values:

Bob ---- £10,000

Alice ---- £20,000



My tables contain data relating to the same topic: people; so when I set up the EMPLOYEES table I set up a Foreign Key in order to link the two tables for easy looking-up of EMPLOYEES and VITALS relations for the same people. Here I have only one option as only the ID and NAME attributes correspond, so my Foreign key for the EMPLOYEES table is ID and it references a Candidate key (in this case the Primary key) which is the NAME attribute in the VITALS table.

The Foreign key relationship can then be used to guard my database against inaccuracy - let's assume I have used the SQL 'CASCADE' option (see Wikipedia page). This might be because when Bob or Alice's STATUS is changed to "Dead" I will delete their entry and so I want the Foreign key relationship to mean that their entry is simultaneously deleted from the EMPLOYEES table - so I'll know to stop paying them...

Answer 3

Yeast Infection Cure Secrets - http://YeastCured.uzaev.com/?dIlm