Setting up Foreign key relationship 2

[oaklandar]

Here is how I have my two tables set up in my Access 2000 database.

[b]Customer Table[/b]
CustomerID  CustomerName   CustomerPhone
1           Jones          111-2222
2           Rogers         334-5543
3           Smith          233-1111

[b]Project Table[/b]
ProjectID  CustomerID  ProjectName
1           1          first Project
2           2          project B
3           3          advanced Project
4           2          next project

CustomerID in Customer Table is a Primary unique key.
ProjectID in Project Table is a Primary unique key.
CustomerID field in Customer Table has a One to Many relationship with CustomerID in Project Table which is how I set it up in the Access 2000 GUI Relationship tool.

Is CustomerID in Project Table a Foreign/Secondary key?
And if so what kind of Relationship do I set it up in the GUI Relationship tool??

Please advise.

 

[johnherman]

The Customer Key in the Project Table is a Foreign Key. If all Projects MUST have a Customer then this is a "defining relationship" and the field is NOT NULL and should be validated by a lookup rule to the Customer Table.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[willir]

oaklandar

Well done on defining your relationships and such.

As explained by johnherman, CustomerID is the primary key in the Customer table, and a foreign key when refrenced in other tables.

When you define your relationships with the GUI interface, you are prompted to define the relationship type.

You should just about always enforce referential integrity. Cascading updates and deletes are per your preference -- when you update / delete the customer table, corresponding actions occur on the related table. In your case, because you are (correctly in my opinion) using an ID number, the update feature is unlikely to occur. (If you had used the customer's name for the primary key, and there was a name change, then the update feature would be useful). I personally avoid using the cascading delete feature -- delete a customer and suddenly corresponding projects are deleted. I would normally handle this type of action via programming.

As per mandating a CustomerID for a Project -- depends. Can you have a project without a Customer? Does it seem likely that a Project may exist, and then you hunt for a customer?

Note that even if you do not make it mandatory to have a CustomerID for a Project, by enforcing referential integrity, you accomplish two things. 1) If you assign a Project to a Customer, the CustomerID has to valid; 2) If you delete a Customer, you first have to delete the affected Projects, or re-assign the Projects to another Cusomter, or remove the CustomerID entry for the affected Projects.

Richard

 

[oaklandar]

Thanks for all the details. If I am understanding what you say I dont need to add anything more to my GUI relationship tool because a Project will always have only one customer and there will never be a Project without a customer.


The Customer Key in the Project Table is a Foreign Key. If all Projects MUST have a Customer then this is a "defining relationship" and the field is NOT NULL and should be validated by a lookup rule to the Customer Table.
I assume "defining relationship" is not something I make in my relationship but I need to create a lookup rule using vba or some sort of GUI tool.

 

[willir]

The "LookUp" tool you are "looking" for is to use a combo box on the form.

You can use the "LookUp" feature within Mircosoft Access when designing your tables -- from the initial looks, this seems to be a pretty cool tool. For example, you setup the LookUp to use "SELECT CustomerName, CutomerID FROM CUSTOMER" where CutomerID is the bound field. When you look at the CustomerID field at the table level, or from a query, Access displays the CustomerName.

As I said, this seems to be pretty cool -- it just about gives you what you seem to want. BUT this can be a very dangerous "feature". You are never looking at the "raw" data. And if you every decide to upgrade to a full-featured relational database such as MS*SQL or Oracle, this type of "linkage" creates havic. Please review The Evils of Lookup Fields in Tables by Dev Ashish & Arvin Meyer, probably one of the best Access sites on the net.

My suggestion is to stick to defining the SQL statement for your combo box, or using a query.

Richard