Cannot enforce referential integrity

[wordsmith]

Dear All,

Please see attached word document with a screenshot of my tables and their relationships. I'm trying to link from Table:Line,Field:Line to Table:Shift-Line and similarly with the shift table and access is not able to maintain referential integrity.

Can anyone explain why? and generally what I've done wrong with my relationships please?

Much Appreciated

Keji

 

[KenHigg]

It would help if you indicated which kind of link you are trying to set up (1 to 1, 1 to many, etc). Also, it may help if you indicate which flds are pk/fk and/or if they are set to only accept unique values...

 

[wordsmith]

Hi Ken,

thanx for having a look.

the bold font fields are all set as primary keys. The link between Line and Shift_Line should be a one to many from Line to Shift_Line, (I think), similarly with the Shift table

 

[KenHigg]

Is the Line fld in Line table set where it cannot accept duplicate values? Also, do any of these tables already have data in them?

 

[wordsmith]

Yes the Line field in Line table and the Shift field in Shift table are set to no duplicates,and yes there is data in it....Shall I allow duplicates? I'll get rid of the data now

 

[KenHigg]

Your existing data may violate some rules. Yes, deleting this type data may help. Also, I'm thinking that for every record on the many side, you'll need a like record on the one side...

 

[Pat Hartman]

Your schema needs a lot of work. Most of your relationships are wrong as are your primary keys. Most of the time, the primary key will be a single field and frequently that field will be an autonumber. Conventionally, we name these key fields - SomeNameID. You seem to have included irrelevant fields in the keys. For example you have included a person's surname in the key along with the ID field. The relationships are ALWAYS from the primary key (ID field) to a long integer. If you want to see the data from the related table, you create a query that joins them. Many of your joins seem to be on text field to text field and to get around the requirement that the text field be a key, you have created compound primary keys. Study the relationship diagrams of the templates that Microsoft posts on their download page.

 

[wordsmith]

Thanks for the reply, i've amended the tables so they have just one primary key, which is the unique identifying autonumber.

the tables which have more than one key are linking tables between 2 other tables, should these be queries? or are they ok as they are now, i have created some queries using these tables e.g. holidays by week, holidays and sickness by operator etc.

 

[Pat Hartman]

You still have a join between non key fields. WeekDesc is a non-key field. WeekID should be used in Holidays and Sickness rather than WeekDesc.
You are joining Skill to skill between Skills and People. It should be SkillID to SkillID.
You are joining ShiftLine to ShiftLine between Shift_Line and People. It should be ShiftLineID to ShiftLineID.

And there may be more. This is what I saw at a glance.