Help me understand primary and foreign keys

[Mljohn]

I'm new to access. I understand some basics but I am having a hard time understanding keys and relationships.

I'm working on a sales management database.

I understand that a primary field in a table is to keep the data unique. For example the "company" field could be the primary field to keep from duplicating company names.

What is the difference between using the "company" field as the primary key and a "Company ID" field with the auto number type data?

If I use a "Company ID" as the primary key what are the benefits over using the "Company" field?

Thanks for the help and I hope you will follow this thread as I try to understand Primary and foreign keys and relationships.

Matt

 

[georgedwilkinson]

Numbers are faster to index than text fields. It is assumed that a number key that has absolutely no meaning to the user will never change. If you change a text key, you have to go to all the places that text key exists to fix your data. This is a significant problem in the IT world.

It is almost always best to use a surrogate key (i.e. NOT the text key that has meaning to users) as your primary key.

 

[ajetrumpet]

What is the difference between using the "company" field as the primary key and a "Company ID" field with the auto number type data?

the difference is basic normalization and devlopment standards. i am willing to be that everybody who develops in access uses the autonumber as a primary key in most tables. I know I do, simply so I don't have to track whether or not I've already used an identifier. It is a very good practice, and that's actually the reason why autonumber exists.

 

[Boro]

Sometimes you could use system generated no, sometimes you have to type in numbers eg. green card no. I have developed some data bases but
have not need for primary and secondary keys Just one key was always
enought. Foreign key is when one each of two tables which you want to link have their own keys but they are not same. In that case you have to
have second key in one of them to link them. I think that this is a case, but as i tell you in practice i have not such a case.

 

[neileg]

The main reason I would use a surrogate key (autonumber) over a natural key like company name is that even where you are absolutely certain at the outset that there will be no duplicates, it later turns out there are.

In the specific case of company names, companies can change their name. If you have used the name as the PK, you'll have to rework the entire record. If you have a surrogate key, you just change the company name field.

 

[Rabbie]

Sometimes you could use system generated no, sometimes you have to type in numbers eg. green card no. I have developed some data bases but
have not need for primary and secondary keys Just one key was always
enought. Foreign key is when one each of two tables which you want to link have their own keys but they are not same. In that case you have to
have second key in one of them to link them. I think that this is a case, but as i tell you in practice i have not such a case.

Just to clarify what Boro has said. A foreign key is a way of modelling a relationship. Ie In a One to many relationship You would store the primary key of the parent record as a foreign key in the child record. This makes it easy to write a query to get the parent record with each of its child records.

Despite what Boro says this is very common in a properly normalised database. For example

in a DB which has a Customer table, An Order table and an OrderDetail table you would have foreign keys in the order records and in the Order detail table.

 

[Mljohn]

Thank you everyone for your help. This is what I understand from what you all have said.



Primary key should use the autonumber. Numbers are faster than text fields for the database to process. With autonumber as the primary key there are no worries about future changes in a text field primary key.


A foreign field is the primary key name field (autonumber) from the parent table entered as a number field in the child table. This field links back to the parent table to show relationship.


In a One to Many relationship the "Many" would have foreign keys.


Thanks for your help. I hope you will continue helping me with my other questions on this subject.


Matt

 

[Mljohn]

Question: How does a autonumber primary key keep out duplicate records?

For example. My sales contact table has a "company" field. Say I have a company named "Wendy's". There are lots of Wendy's.

How do I get my database to allow companies with the same name and different locations but not allow companies with the same name and the same locations?

 

[boblarson]

Question: How does a autonumber primary key keep out duplicate records?

It doesn't. However you can do what I do and use a form for input where it doesn't allow an update if the value exists (I use a DCount to check) when checked in the Form's Before Update event.

OR you can set a MULTI-FIELD INDEX on the name and location in the table.

 

[Mljohn]

What is the benefit of using the DCount to not allow an update in the form's Before Update event over using a "multi-field index"?

How do I use Dcount? I don't understand expressions well yet.

Matt

 

[boblarson]

What is the benefit of using the DCount to not allow an update in the form's Before Update event over using a "multi-field index"?

You can give the user a more detailed explanation and you can give them a way out as well.

How do I use Dcount? I don't understand expressions well yet.

Private Sub Form_BeforeUpdate(Cancel As Boolean)
   If DCount("[CompanyNameFieldNameHere]", "YourTableNameHere", "[CompanyNameFieldHere]=" & Chr(34) & Me.YourTextBoxWithCompName & Chr(34) &  " And [LocationFieldNameHere]=" & Chr(34) & Me.YourControlWithLocation & Chr(34))> 0 Then
  Cancel = True
     If Msgbox("This name and location already exists." & vbCrLf & _
        "Do you want to try again?", vbQuestion + vbYesNo, "Duplicate") = vbNo Then
        Me.Undo
     End If
  End If
End Sub

The Cancel = True stops the update from attempting and the Me.Undo removes the values from all of the fields if the user doesn't want to try again.

The Chr(34) in the code is a double quote " which makes it so you don't have to use single quotes (') or triple double quotes (""") which would be necessary if you wanted to ensure you don't have problems if there is a single quote in one of the names.

 

[Mljohn]

Thank you Boblarson but that is way over my head. I don't know what that means or how to use it. I'm not there yet.

Thanks though for the explanation of Dcount's benefit of giving the user better understanding of what to do and a way out if they can't continue.

I guess I'm using the Multi-Field Index which I kind of understand.

Very kind of you to write the code.

I do find your code surprisingly beautiful and look forward to understanding it.

Matt

 

[boblarson]

Thank you Boblarson but that is way over my head. I don't know what that means or how to use it. I'm not there yet.

Thanks though for the explanation of Dcount's benefit of giving the user better understanding of what to do and a way out if they can't continue.

I guess I'm using the Multi-Field Index which I kind of understand.

Very kind of you to write the code.

I do find your code surprisingly beautiful and look forward to understanding it.

Matt

What don't you understand? We can work through it.

The first part of the DCount is the field which we want to look at and surrounded by double quotes (you need the square brackets if your field name has spaces).

The second part of the Dcount is the table where you are looking for the data.

The third, and final part, is the criteria. In this case we need to check to see if the company name matches the text box value we've entered on the form. Then there is an AND because we also need to check to see if the location exists for that company name.