Primary Key & Foreign Key

Basically i am using the w3 schools tutorials to start a Joins tutorial.

I have a person table with the columns, FirstName LastName Address City Id,
Id being my primary key in this table. When i went to create a new table called the Orders table.. I stumbled across the thought, Right i need to add the Id from the person table into this as a foreign key so decided to name is Person, my query is i don't understand is if i set a primary key in my person table to Id and then a foreign key in the orders table to Person, how does the table know that these are the same as in the Person table it is ID and in the order table it is Person,

Surely it cant just be because the data types are the same?

As you can see i am a bit confused by the whole primary key and foreign key business when it comes to actually putting it into practice i understand the basics that a primary key is a unique column designated to idenitfy all table records. And a foreign key being the primary key of another table.

I hope that makes sense. Any answers will be massively appreciated as this seems to be the first major stumbling block and seems as if it needs to be nipped in the bud before i can start moving forward at pace in the database world!

After you issue that command, SQL insures that any non-NULL "person_id" value you add to the Orders table has a matching row in the Persons table (if a FK is NULL, SQL doesn't do the look up), or SQL will simply reject the INSERT.

Btw, yes, a FK requires an exactly-matching unique index in the referenced table.

Maybe i didnt really structure my question well. I am trying to manually create my table. I just want someone to explain the basics in using primary key's and foreign keys when it comes to creating tables - and how for example if one table as primary key of Id, i then use this in another table and call it Person how the other table knows that this is the Id of one table even though it is called Person.

"...explain the basics in using primary key's and foreign keys when it comes to creating tables..."
Primary and foreign keys are optional columns that allow you to index, sort, and link tables.

"...for example if one table as primary key of Id, i then use this in another table and call it Person how the other table knows that this is the Id of one table even though it is called Person."
The "other" table - the one with the foreign key - doesn't know anything about it. YOU popluate the foreign key column when you store data in the "other" table, so it's YOU that is creating the association. The foreign key just gives you the connection between the two tables.

"Prodigy"? You don't have to be prodigy to realize that copying the first definition from a Google search doesn't really mean you understand the topic, let alone have genuine expertise in it. Presenting it as such just seems bizarre to me.

Superjinx, i have been where you are and i sympathize, allow me to offer some clarification and expansion on the above comments.

When you create a table, good practice is to create a column which will be used to uniquely identify a record in that table. Typically you might name this column "id" or something similar. This column is known as the "Primary Key". You can also use a number of columns to create your primary key (this is known as a compound key). But as you are just starting you may not need to look into this just yet, just be aware that it exists.

An example of the above might be something like this (say your table is called tblEmployee):

Here, you can uniquely identify each record using the ID field, notice you have 2 Peter's, but they have different ID's. Using the Name wouldn't make it unique, which is why we have assigned an ID and made this the PK.

Ok, that's Primary Key's explained (i hope). Now for foreign keys....

A foreign key is simply a column in another table, which is to contain the primary key of another table.

Here is an example following on from the previous. We have a table called tblHoursWorked, and the structure of this table looks like this:

RecordID int (pk)
DateWorked datetime
Employee int <-- Notice this is an int and not a varchar
HoursWorked int

The PK in the above table is RecordId (remember, best practice, every table should have one), a FK in the above table is Employee. Why is Employee a FK? Simple, because it contains a value that is to represent a PK in another table (tblEmployee). What does the data in the above table tell us:

- Peter (101) worked 3 days, 6 hours each day - good lad
- John (102) worked 2 days, 5 hours one day, 6 hours the next, and he must have been sick one other day - tut tut
- Paul didn't work at all - He must be on benefits
- Peter (104) worked 2 days, 5 hours one day, 4 hours the other

Do you see how i got this explanation from the data in the second table?

The presence of the "Employee" field allows a link between tblHoursWorked and tblEmployee (a one to many relationship).

This is Primary and Foreign keys in a nutshell.

Something else to consider and to investigate at some point. Unless you enforce "Referential Integrity" in a database, its all theory, referential integrity enforces certain rules, one being that you cannot save a value in a foreign key field/column if it doesn't exist as a primary key in the corresponding table (for example, we could not store Employee 119 in tblHoursWorked because there is no employee with this ID).

Ha also apresto I just notice that it was you that wrote that exemplary defition for me just above the reference to Scott pledger, and can very much see how the tables are linked using the foreign key and how you used employee as the fk in the second table! My brother is what some might call a prodigy genius, and hope to go tonight to show him what I now understand. Wish I could give you the 500 points. Thanks a lot!

I was answering the q asked:
"
how does the table know that these are the same as in the Person table it is ID and in the order table it is Person,

Surely it cant just be because the data types are the same?
"

He didn't actually ask for a generic description of FK and PK, which made sense to me, since that's available all over the net. Of course verifying that it's accurate isn't quite so easy; as with your answer, he might have to settle for partially accurate with some inaccuracies.

Its ok, I wouldn't have posted in a "resolved" question if i was here for the poitns :) I've been where you are and i understand that sometimes getting an explanation instead of "an answer" is like squeezing blood from a stone.

Best of luck with your studies, and don't be afraid to ask "why"

ScottPlebber, you did indeed answer the question, but with absolutely no regard for the competencies of the author, well done, what a shining example of an expert you are!

I can't possibly know the expertise level of an anonymous poster, nor will I assume because he/she is doing tutorials: those could cover a wide range of subjects.

The author's own follow up q since asked specifically how SQL "knows" the keys are connected, not the basics of what those keys are:
"
how for example if one table as primary key of Id, i then use this in another table and call it Person how the other table knows that this is the Id of one table even though it is called Person.
"

"How the other table knows" is as I stated: you have to explicitly tell SQL about the relationships between values in tables. The exact syntax was just in case it was needed, and because the SQL command itself can be Googled to get an explanation of what the code is doing, and the reasoning behind doing it.

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful.
Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…