Invalid Field Definition - Unable to set up cascade update (1 Viewer)

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
I have a database with the following design:

TableOne - Main records.
TableTwo - Subsidary Records (1)
TableThree - Subsidary Records (2)
TableFour - Optional Subsidary Records (1)
TableFive - Optional Subsidary Records (2)

There is a many-to-many relationship from TableOne to TableTwo and TableThree.

There is a one-two-one relationship from TableOne to TableFourand TableFive.

Enforce Referential Integrity is enabled for all relationships.

Cascade Update and Cascade Delete is enabled for all but the relationship from TableOne to TableFive. When I try to set Cascade Update (either alone or as well as Cascade Delete) I am informed that there is an:

"Invalid field definition 'id' in definition of index or relationship."

'id' is the long integer autonumber field (Primary Key) in TableOne, which has already been successfully linked to the other tables. I've checked the field properties in TableFive and its primary key field is set to Long Integer as well (NOT autonumber).

Any ideas why this is happening, and how the problem could be resolved?

Tim
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
Don't know for sure if this is it, but from your description:
Tim L said:
I've checked the field properties in TableFive and its primary key field is set to Long Integer as well (NOT autonumber).
you don't have the keys set properly -
Table 5 should have it's own unique primary key. Then, the primary key from the other table would then be the foreign key (not marked as a key mind you) in Table 5.
 

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
Bob,

Thanks for the very prompt reply.

TableTwo and TableThree have their own Primary Key field as they are operating on a one-to-many relationship, whereas TableFour and TableFive are operating on a one-to-one relationship so their Primary Key is the link field to TableOne.

This works for TableFour, so I can't understand why it isn't working for TableFive.

Tim
 

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
As requested. It's probably something *really* simple.

Tim
 

Attachments

  • Task-Bare.zip
    35.9 KB · Views: 961

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
Okay, yes your keys were all messed up. I fixed them. What you failed to understand is that each table has it's OWN primary key and then uses foreign keys to link to the other tables. If you take a look at what I did, both in the tables and in the relationships, you will see that I named the foreign keys in each table the same as the primary key in the other table so that it is easier to identify the links. It is good to name each primary key differently so that you don't have "id" for each and then you can't visually see what is what.

Also, I noticed you had one lookup field being used in a table. STAY AWAY FROM THOSE as they will cause you pain and suffering. Use forms instead and combo boxes and/or list boxes to do the same thing. Also, if you link your lookup tables in the relationships, I would not click on Maintain Referential Integrity nor the Cascading updates/deletes as it will cause validation issues.
 

Attachments

  • Task-Bare.zip
    32.9 KB · Views: 1,644

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
Bob, Thanks.

I notice that there is an additional entity of tblTasks (tblTasks1) in the relationship view, I realise that this is still the same table, but what purpose does it serve to have it in the relationship view?

I also notice that the Remarks and Progress tables have had their primary keys removed, so that there is no longer an indexed field, was this deliberate, and if so why? (I thought that it is good practice to have an indexed, non-duplicate, primary key field.)

I got used to setting lookups in the table design as it meant that I could ensure that when the table was viewed the 'readable' data would be displayed and when creating combos (etc.) on forms the settings would automatically be imported, rather than having to adjust the settings potentially multiple times. If you say it will only lead to suffering then I will desist, but if you would be able to provide a more verbose reason for doing so I would be grateful, then, if I get to impart the same knowledge onwards, I will not just have to answer "because it's bad for you" :)

Thanks again for your help.

Tim
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
The duplicate table was accidental. I must have clicked twice. The primary keys on those tables should be there and again when I modified the tables I missed adding them back. The only key should be the autonumber and not a composite key though.
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
Lookups in tables cause problems with queries and reports, so you should avoid them. They work basically the same in forms and you can just set up the combo boxes so that they show the applicable descriptions, but they save the ID number in the table.
 

Banana

split with a cherry atop.
Local time
Today, 08:06
Joined
Sep 1, 2005
Messages
6,318
Bob Larson,

Would you mind explaining why checking Enforce Referential Integrity and Cascade Update is a bad thing? I already know there are some problem with Cascade Delete, but I've seen other people on the board recommending checking the RI and Update if you want to prevent inconsistent or missing data?
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
When using for normal tables, it is great. But, when linking LOOKUP tables, you will likely get errors while trying to add data in your main tables that say, "You can't add a record at this time because an associated record is required..."
 

Banana

split with a cherry atop.
Local time
Today, 08:06
Joined
Sep 1, 2005
Messages
6,318
That's right.

So for look up tables, I'd be better off using other way to requiring data from users than using RI? (e.g. I have a lookup table that I would want to require my user to fill in everytime)
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
It can vary depending on your situation. you can try using RI, but if you get that error, you'll know why.
 

Banana

split with a cherry atop.
Local time
Today, 08:06
Joined
Sep 1, 2005
Messages
6,318
Cool. Thanks for the thought-food. :)
 

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
My thanks too, Bob.

I just re-read your comment about the linking and RI matter and realised that I think you were referring primarily to the linking and RI of the lookup tables, not tables in gereral. If I have understood that correctly then I think that things are a lot clearer.

Tim
 
Last edited:

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
Bob,

Having had more time to look at your suggestion I notice that the one-to-one relationships have been lost from TableOne to TableFour and TableFive. A record in TableFour or TableFive may only ever be related to a single record in TableOne, but a record may not necessarily be created (hence the use of the additional tables instead of just creating a larger TableOne).

Am I stuck with the one-to-many relationship in your suggestion or is it possible to create the two one-to-one relationships that I originally desired?

(And what really gets my goat is that I was allowed to create the Cascade Update for one of the relationships, but not for the other.)

Tim
 

boblarson

Smeghead
Local time
Today, 08:06
Joined
Jan 12, 2001
Messages
32,059
Instead of saying TableFour and TableFive, can you tell me the true names so that I can look? Also, what are the one-to-one relationships you are wanting?
 

Mike Smart

Registered User.
Local time
Today, 08:06
Joined
Feb 14, 2007
Messages
53
When using for normal tables, it is great. But, when linking LOOKUP tables, you will likely get errors while trying to add data in your main tables that say, "You can't add a record at this time because an associated record is required..."

Hi Bob

I'm interested in this observation. If the main table has no default value for the foreign key (ie it will default to null) wouldn't that cure the issue you refer to?

Assuming an autonumber primary key in the related table Access always defaults the foreign key to 0 strangely and that does cause the problem you refer to.

Would really appreciate your experiences on this one.
 

RoyVidar

Registered User.
Local time
Today, 16:06
Joined
Sep 25, 2000
Messages
805
Here is the original database with the relationship asked for.

You had a reference to the Microsoft DAO 2.5/3.5 compatibility library, which is something needed for backwards compatibility with versions probably not run anywhere anymore (2.0 or 5.0?). That's probably not needed.

Else, what I did, was only delete the relationship, then create it again (see attached).

Note - when dealing with 1:1 relationships, the Access GUI needs a bit of assistance in determining which is the referenced table, and which is the referencing table. For ordinary 1:m, it seems the index on the fields is what Access uses, but since both fields in a 1:1 relationships would have UNIQUE constraint (Yes, no dupes), then it becomes a challenge.

I am assuming that tblTask is the referenced table (main table, mandatory table, parent table or whatever you whish to call it), and tblTaskEquipmentInfo is the referencing table (optional table, child table), which may contain information. Schematically, it could be described like this
Code:
Referenced table              referencing table
---                               ---
|A|  1 - 1 ---------------- 0 - 1 |B|
---                               ---

1 occurrence of A can be related to minimum 0 maximum 1 B
1 occurrence of B must be related to one and only one A

To make Access understand what you intend to do, you must either drag'n'drop from the referenced table to the referencing table (in your case, the ID field from the tblTask table, and drop onto the link field on the tblTaskEquipmentInfo), or you could use the "Create New" button after drag'n'drop in either direction. Then on the left side, select the referenced table (tblTasks), then in the right combo, select the referencing table (tblTaskEquementInfo).

(you will be able to observe this by examining the two 1:1 relationships you already have, note which table is placed in he "table/query" side (left), and which table is placed in the "Related Table/Query" part)

Finally, since you are using autonumber as primary key in your referencing table, cascade update isn't needed, as you cannot change autonumbers. Cascade updates are only relevant when using natural keys.
 

Attachments

  • Task-Bare.zip
    21.7 KB · Views: 204
Last edited:

Tim L

Registered User.
Local time
Today, 15:06
Joined
Sep 6, 2002
Messages
414
Bob, my apologies for not responding to your request sooner, for some reason I did not receive any new message notification for the thread until Roy's reply.

tblTaskEquipmentInfo and tblFirmInfo were TableFour and TableFive.

Roy,

thanks for your excellent explanation. It's easy once you know how... Always drag FROM the reference table!

Thanks again to both of you. The time you have taken to reply is much appreciated.

Incidentally, I did eventually notice that tlbTaskEquipmentInfo should have been tblTaskEquipmentInfo.

Tim
 

Users who are viewing this thread

Top Bottom