Solved

Primary Key & Foreign Key

Posted on 2013-01-29
21
364 Views
Last Modified: 2013-02-06
Hi,

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!

Thanks, SuperJinx
0
Comment
Question by:SuperJinx
  • 6
  • 5
  • 5
  • +1
21 Comments
 
LVL 33

Expert Comment

by:paulmacd
ID: 38833051
Change the name of the foreign key to ID_Persons.

Then when you select, it will be something like
SELECT *
FROM Persons
INNER JOIN Orders ON Person.ID = Orders.ID_Persons
0
 
LVL 33

Expert Comment

by:paulmacd
ID: 38833055
And no, a foreign key is NOT a unique/primary key in another table.  A foreign key is a way to associate two tables that are related to each other in some manner.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38833108
>> 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 <<

You have to explicitly "tell" SQL that.

ALTER TABLE dbo.Orders
    ADD CONSTRAINT Orders__FK_person_id
    FOREIGN KEY ( person_id ) REFERENCES dbo.Persons ( id )

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.
0
 

Author Comment

by:SuperJinx
ID: 38833109
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38833246
Pls refresh this q and look at my previous comment :-) .
0
 
LVL 33

Expert Comment

by:paulmacd
ID: 38835349
"...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.

http://databases.about.com/od/sqlserver/a/Foreign-Keys-In-Microsoft-Sql-Server.htm
0
 

Author Comment

by:SuperJinx
ID: 38835548
Legend! Cheers!
0
 
LVL 33

Expert Comment

by:paulmacd
ID: 38835563
Ta!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38835701
Some false info:

>>
"...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.
<<

but if that's what you wanted,  you got it!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 33

Expert Comment

by:paulmacd
ID: 38835773
Passive-aggressive much [ScottPletcher]?  

Why not be a Prodigy and explain what's wrong, why it's wrong, and offer the correct information.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38836094
"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.
0
 
LVL 23

Accepted Solution

by:
apresto earned 500 total points
ID: 38839607
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):

EmployeeID            EmployeeName
---------------------------------
101                  Peter
102                  John
103                  Paul
104                  Peter

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 data in this table might look llike this:

RecordId      DateWorked      Employee         HoursWorked
5            2013-01-28      101               6
6            2013-01-29      101               6
7            2013-01-30      101               6
8            2013-01-28      102               5
9            2013-01-30      102               6
10            2013-01-28      104               5
10            2013-01-29      104               4

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).

I hope this helps :)

Apresto
0
 
LVL 23

Expert Comment

by:apresto
ID: 38839654
Scott Pledger, dumping this :

ALTER TABLE dbo.Orders
    ADD CONSTRAINT Orders__FK_person_id
    FOREIGN KEY ( person_id ) REFERENCES dbo.Persons ( id )

into a question where the first line is:

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

is as good as responding in Chinese!
0
 

Author Comment

by:SuperJinx
ID: 38839761
Whoever you are apresto, you speak sense
0
 

Author Comment

by:SuperJinx
ID: 38839805
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!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38840339
Aprassto:

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.
0
 
LVL 23

Expert Comment

by:apresto
ID: 38842988
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!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38843921
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.
0
 

Author Comment

by:SuperJinx
ID: 38859857
What?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now