Referential integrity – linked tables

Topic

New Reply

Access 2K on Windows 98
I have a database (#1) with tblSubmission, which has a Submission_id autonumber field.
I have another database (#2) with a tblAuthors, which has an au_id autonumber field.
I have linked tblAuthors into database #1.

I have created a tblSubmissionAuLink table, which contains Submission_id (long)and au_id (long) fields, which are set to defaults of ‘Null’, and the combined fields form the primary key.

I can establish a relationship between tblSubmission and the linking table (with referential integrity enforced, but cannot enforce referential integrity on the relationship between the linking table and tblAuthors (all options are greyed out).

Can anyone explain why this is happening, and if there is a work around?

Many thanks,

kiwi44

Reply | Quote

Replies

I don’t think you should ever link tables via the auto number fields in only leads to problems. Create a unique index field and use that to link in its place.

It appears you are also tying to link on different field types, I’m surprised that your first link worked. Ensure that the linked fields are both the same format.

Reply | Quote

If I understand you correctly, I should not be using autonumber fields in my database? I know that I should not be using them if I need to account for numbers of records, etc. (e.g. invoice numbers), but in this application there is normally no need to have numbered lists in the output – it is a list of projects that are normally listed alphabetically. The autonumber is simply a record identifier for internal database purposes – is this also wrong?

I also do not understand the comment about trying to link on different field types – autonumber fields are type long, as are the fields I want to link to. The only difference is that one has the number automatically generated, the other does not. If they were truly different, wouldn’t Access give me an error message?

Referential integrity can be automatically applied using the link wizard if the table is imported, indicating that the field types are correct, but not if it is linked.

Any further explanation/advice gratefully received!

kiwi44

Reply | Quote

I have to disagree with Caz. Autonumbers are unique fields and are perfectly valid for linking. I prefer them to actual data fields since there is no logical reason to ever try to change an autonumber, while the same cannot be said for any field that contains real data. Autonumbers are simply autoincrementing long integers, so they will join on any long integer field.

Your problem is that you’re trying to enforce referential integrity in the wrong direction. The join table should contain one foreign key from each of the other two tables for each record. The referential integrity is from each of the other two tables into the join table so that each primary table enforces referential integrity on its own key.

The only time you ordinarily see grayed out options in referential integrity is when one of the tables you’re trying to relate is actually in a different database or when you try to establish the relationship from the front end on linked tables. You can’t enforce referential integrity across databases at all in Access, only on tables within the same database, and you can’t create it from the front end on linked tables.

Reply | Quote

Many thanks – I thought I had the right idea, but I just couldn’t find it documented that you couldn’t enforce referential integrity between databases.

I am also pleased to see that I was OK with using autonumber fields for linking – I would have hated to have to do all that redesigning.

Now back to the question of how to actually input data into all the different tables I have …

kiwi44

Reply | Quote

I’m glad your all sorted now.

I maintain that it is bad practice to join on autonumber fields rather than your own unique index. It can become an issue when restoring databases, especially when data has been deleted. This is probably more applicable in large database applications rather than Access.

Reply | Quote

Could you explain what you mean by autonumbers becoming an issue when data has been deleted? It sounds like you’re concerned about consecutive numbers, which is a whole different issue. Autonumbers should never be relied on for sequential numbering, since they can be set for random sequence (and are, if you convert a database to replication) rather than incremental, and they may reuse deleted numbers at the end of the recordset after a compact. They aren’t intended to be data, only to be keys.

I’ve never had any problems with autonumbers in the 9 years since Access 1.0 was released, so if you have had, it would be useful if you shared that information with the Lounge.

Reply | Quote