Vb/Access/ADOControl!

kegan5

I'm completley stuck with the whole Primary and Foreign Key idea!

I'm fine with the whole, linking a database into VB however i cannot work out these Primary and foreign Keys.

SAy for example i had Customer_Id as primary in one but wanted to use it in another table too would that make it Foreign?

So, if I want to use Cutomer_Id at any point in any other table it would HAVE to be a foreign key???

PLEASE HELP!

cgarvey

Originally posted by kegan5
I'm completley stuck with the whole Primary and Foreign Key idea!

I'm fine with the whole, linking a database into VB however i cannot work out these Primary and foreign Keys.

SAy for example i had Customer_Id as primary in one but wanted to use it in another table too would that make it Foreign?

Yup, or it should at least.

So, if I want to use Cutomer_Id at any point in any other table it would HAVE to be a foreign key???

It should be, yes.

So in the eaxample below, the customer_id is the foreign key in the orders table. How to set up primary/foreign keys depends on what database you are using, but Access and MS SQL Server have drag'n' drop ways of doing this.


Customer
=======
id
name
dob

Orders
=====
id
date
total
customer_id

Talliesin

"Primary Key" has two different but related concepts.

Considering a table in isolation a primary key is a column or columns (the primary key is not restricted to a single column, it is frequently useful to make it span 2 or more), that uniquely identifies a row.

As such you can use the primary key to refer to the entire row, since there will be no ambiquity about which row you mean, and this has a variety of advantages, in particular when you perform UPDATES, and also with relationships which is the next concept.

Most often the Primary Key is a single IDENTITY column, that is when a new row is added it is given the next integer value that hasn't so-far been assigned (guaranteeing it's unique within that table).

In a relationship you are stating that the only allowable values for a particular column in one table is those that are contained somewhere in a particular column in the other.

In doing this you are stating a relationship between what the two rows represent. As such it is most common that the column you use for one end will be a table's primary key.

Hence that end of the relationship gets referred to as "the primary key", because it normally is, and the other as "the foreign key".

Because of this fact the column playing this role in a relationship will often be referred to as the primary key.

Hence you have two slightly different, but frequently overlapping definitions of "primary key":

1. The column or columns used to uniquely identify a row in a table.
2. The column used "one" end in the one-to-many relationships that exist between tables.

Related to the concept of "primary key" is that of indices.

You should have indices on any rows on which you would frequently be searching, either explicitly (the row appears in a WHERE clause), as part of a JOIN (there is an implicit search on the rows mentioned in the ON part of the JOIN) or during an update to a related table (which will require a search to see if the value in the related table is allowed, i.e. there is an equivalent value in the row in the table we are considering).

These indices are used in a variety of different ways. They are stored in a manner that makes look-up more efficient than if they didn't exist, especially the clustered index (which is used to order the table and hence the most efficient one to search).

Because primary keys are searched implicitly in a lot of situations, as well as perhaps being searched explicitly they are always indices, and are often a good candidate for a clustered index.