Relationship problem

[Kon]

I have a table master and another headmaster
in master there are the fields ; date,customerid,workdescription and the
headmaster date, customerid.
All this fields are primary keys. So I have

master headmaster
3 primary keys 2 primary keys
Can I have a referential Integrity with cascade update and delete related
records between these tables?

 

[KARL DEWEY]

There can be only one primary key to a table. Sometimes people make a
combination primary key that consists of multiple fields.

To have a relation you must have a primary key in a table - each record will
be unique - no duplicates. The table that you want to relate must have a
field that is the same datatype as the primary key field - duplicates allowed
- and contain the same data in the related field.

 

[Tom Wickerath]

Hi Kon,

A table can only have *one* primary key. This primary key (PK) can be a
combined field PK, which is apparently what you have.

Your current design would require that you add the workdescription field to
the headmaster table. Not very efficient, in my opinion. My suggestion is to
add a new autonumber field to your master table, and set this as the primary
key instead. You can create a unique index on the combination of
date,customerid, and workdescription in the master table to prevent
duplication. Then, add a new numeric / long integer foreign key field to the
headmaster table. This way, your relationship involves one field only.

You might want to read the following article:

http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1

Post back if you are not sure how to set a unique index on > 1 field.

http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1

Post back if you are not sure how to set a unique index on > 1 field.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a table master and another headmaster
in master there are the fields ; date,customerid,workdescription and the
headmaster date, customerid.
All this fields are primary keys. So I have

master headmaster
3 primary keys 2 primary keys
Can I have a referential Integrity with cascade update and delete related
records between these tables?