[ammonkc]

can anyone explain to me how to use primary and foreign keys in a mysql schema. how is linking two tables together with primary and foreign keys useful? if I do a select on a table will I be able to pull data from fields in a second table that is linked by a foreign key?
for example:

Table_a
request_id (primary key)
request_type
usr_id (FK)

Table_b
usr_id (primary key)
usr_name
usr_email

can I do this with my select statement:

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '1234'

if not then how would I do this? and what is the advantage of putting my data into 3rd normal form?
I know that I learned this before, but it has been a long time since I've done this stuff, and I need to relearn it. I would really appreciate any explaination. thank you in advanced.

[UnrealEd]

you should check out the JOIN function from mysql, that's the way to search two tables at the same time.

I don't know how to do it myself, but here's a link:
http://dev.mysql.com/doc/refman/5.0/en/join.html

Hope it helps you out,
Greetz,
UnrealEd

[TLLOTS]

Originally Posted by ammonkc

can anyone explain to me how to use primary and foreign keys in a mysql schema. how is linking two tables together with primary and foreign keys useful? if I do a select on a table will I be able to pull data from fields in a second table that is linked by a foreign key?
for example:

Table_a
request_id (primary key)
request_type
usr_id (FK)

Table_b
usr_id (primary key)
usr_name
usr_email

Using primary keys and foreign keys is a much better method than the alternatives, such as keeping two tables, but holding the user name in the request table instead of an id. You'd still be able to join the tables together that way (as I'll talk about later), but you run into problems.

One of the most immediate issue's is that it makes a user changing their name an absolute pain in the ***, as not only do you need to update the user name in Table_b, but you also need to update the user name in every entry in Table_a, otherwise you'll end up with issue's such as requests pointing to a non-existant user.

can I do this with my select statement:

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '1234'

That would almost work, except you'd get a lot of rows returned, despite there being only one request with an id of 1234. This is because the tables don't use the primary and foriegn key's automatically, so you need to join them together manually, otherwise you'd get every user's data returned along with it.

E.g. if the tables contained the following

Code:

Table_a
request_id | request_type | usr_id (FK)
1                     'mail'              3
2                     'email'            2
3                     'email'             1
4                     'mail'               2

Table_b
usr_id | usr_name | usr_email
1            'bob'        'bob@email.com'
2            'jane'        'jane@email.com'
3            'joseph'     'joseph@email.com'

Your query (with a request id of 2 instead of '1234') would return

Code:

     usr_name |      usr_email        | request_type
         'bob'      'bob@email.com'         'email'
         'jane'     'jane@email.com'         'email'
         'joseph'   'joseph@email.com'     'email'

This is obviously not what you want, so to make it so it returns only the data relevant to the user who made that request, you'd make your query the following

SELECT usr_name, usr_email, request_type FROM Table_a, Table_b WHERE request_id = '2' AND Table_a.usr_id = Table_b.usr_id

With this query you'd get the following returned

Code:

     usr_name |      usr_email        | request_type
         'jane'       'jane@email.com'       'email'

There is one thing that you may have noticed with the query which may look odd, which is how I refered to the usr_id's with Table_a.usr_id instead of just usr_id. The reason for this is that if you have columns with the same name in tables you're joining together, if you try to refer to one of them, your database won't know which column in which table you're referring to, so you'll get an error. To get around that you have to specify the table its in as I did. I hope that helps ^_^