Compound/composite keys in relationships

[idd]

MS Access 2000 user,

Hi All.

I am designing a system in Which I have found that one table may have a compuond/composite key.

if I am going to show a relationship between the two tables I need a foreign key in the second table, If my first table has two fields which make the Primary key, how do I go about showing the foreign key in the second table.

DO I need to add both parts of the primary key from table 1 to table 2 to show the link. If this is how it should be done then how also do I create the relationships in relationship view.

I am considering creating a new field which will become the primary key. It will be an autonumber field and obviously be unique, I am not sure if this will allow my database to control the fact that the candidates I have for my Primary keys at present need to have a unique combination, which could be controlled if they were used as the primary key.


I hope this makes sense, and I hope someone can help me.

Idd

 

[KenReay]

Hi

You create the foreign key as you said by replicating the columns of your primary key in the 'other' table.

When creating the relationship proceed as normal by dragging between the two tables, when the dialog comes up asking if you want referential intergrity etc, edit the list of columns to include those you wish to base the relationship on.

Even if you decide not to make tyhe compound key the primary key, provided you set it as a unique key, you should still be able to make the relationship Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[idd]

Thanx Ken,

I appreciate your Help.

Idd