Foreign Key ?[Resolved]

[Pirate]

What's is that in MS Access ?

[nswan]

it's is a primary key in another table that is used to link the tables together.

e.g.
tblCustomer
customerID - primary key
name
etc


tblPayment
paymentID -primary key
customerID - foreign key
details etc




the tables are linked by customerID

[Pirate]

Thanks nswan

How would I benefit from this ?

[nswan]

adding relationships between tables. You can do this by going to tools-relationship. This then puts the relationships in automatically when using the query builder.

[Pirate]

I meant Why would I need to build or set these relations between tables ? Will this give me more storage space or what ?

[nswan]

no it's only really a use for making it supposedly easier with the query builder.

I think it'll dramatically increase the size of your database the more relationships you add as well.

I don't usually bother and just add joins manually for each query i build.

[Pirate]

Alright then , I guess I have to read more about it .

Thanks nswan

[Edneeis]

Its a form of index and will increase the speed of your queries, as I believe indexes are checked first or something like that. Also this helps to keep your data integrity because it links the two keys in different tables together to make a relationship. Say you have a Categories table and an Items table like this:

Categories
PK_CategoryID
Name
Notes

Items
PK_ItemID
FK_CategoryID
Name
Notes
Value

Without a relationship then you could have Items with a CategoryID that doesn't match to any category in the Category table. If you have the relationship then it can check and force you to remove the items that belong to a category before removing the category itself or make sure when adding a new item that you use a valid CategoryID.

[Pirate]

Yeah , I remember this example Edneeis . This explains more to me . This needs more practice though . Thanks

[hellswraith]

You should really look into a good db book. I suggest SQL Server 2000 Programming. It is an awesome book. I know you are dealing with Access, but if you learn just the basics of DB design, you can apply it to all DB's.

think it'll dramatically increase the size of your database the more relationships you add as well.

It doesn't dramatically increase the size. Lets say the foreign key is 8 bytes. Even if you have 10,000 records, this is only taking up like 78K (plus or minus a few). That little bit of space is worth the added stability to your data.

Now, a good place to start reading about this type of stuff would be to search for 'entity relationships'. Also, 'normalization' is another term to start reading up on.

Here is a link to get you started:
http://www.informit.com/content/inde...-2E8EFAF74708}

[Pirate]

Great link hellswraith . Now I really realized how important relational database . Thanks for that .