Referential Integrity With Compound Index

[acannon]

Can I use a compound index to enforce referential integrity between tables? If that works can I use cascade update/delete? If that isn't possible I would use an autonumber field in the primary table, but how would I relate records to the subsidiary tables?



I have 12 subsidiary tables to relate to 1 primary table.

I appreciate any and all comments and suggestions!

[ssanfu]

Can I use a compound index to enforce referential integrity between tables?

No.
Indexes (indexing) make it faster to find data in a field that is indexed compared to a non-indexed field. You can use multi-field indexes to make it faster to find records that have the data specified in the fields. You can also use multi-field indexes to limit duplicated data (ie create unique records) in the indexed fields.

It sounds like you are thinking about a multi-field primary key. IMO, this is usually not required.
I always use autonumber type fields for my PK fields. So much easier.

If that isn't possible I would use an autonumber field in the primary table, but how would I relate records to the subsidiary tables?

The autonumber PK field is related to a FK field in another table. Cascade updates/deletes work (but I would use them sparingly).


Can you explain what you are attempting to do? Some background on you r dB?

[ItsMe]

@acannon
Are you thinking about a compound key? Maybe you can. I don't use the relationships window so I don't know.

[ButtonMoon]

Can I use a compound index to enforce referential integrity between tables? If that works can I use cascade update/delete?

The answer to both questions is yes. A foreign key can reference any key (in fact any unique index) in the parent table. The unique key being referenced can be a composite of several columns and it doesn't have to be the "primary" key.

Typically you should try to choose keys that are simple and stable (i.e. unlikely that key values will change). Your keys should also be non-nullable. Unique indexes on nullable columns are not such a good idea and personally I would strongly recommend you avoid them. More often than not it's a good idea for foreign keys to reference just a single attribute (i.e. a "simple" key) but that isn't an absolute rule.

[acannon]

Thanks for the response! In answer to the request of background info and what I'm trying to do, please see my perhaps incoherent rambling below.

This is a multi-functional Project Equipment Management System for engineering projects. We will have a separate db for each project. Each item of equipment is identified by an equipment number of the form AAA-TTT-SSSS.DD. Each element of the number is stored in a separate field in the master table. The length of AAA and TTT are variable, and they are text fields to accept alphanumeric data. SSSS is a numeric value of up to 4 digits and DD is a 2-digit numeric decimal value to identify separate components of the base SSSS number. Every item of equipment has a record in the master table. The related tables contain specific details about each item of equipment, and the details vary with the type of equipment, which is designated by the TTT field. Some types have only 4 general text fields to provide details and some have numerous text and numeric fields to provide the details. The combination of the AAA field and the SSSS.DD field must be unique although each may be used multiple times individually.

Currently I am using VBA in the data entry forms to create a record in the appropriate related table using the AAA and SSSS.DD fields entered in the master table data entry form for new items of equipment. I am using the 2 fields as a compound unique index to prevent duplication.

As a project progresses a piece of equipment may be deleted. For historical purposes we don't actually delete the record; we simply add "D" to the end of the AAA field and then the normal routine of the db application filters those out of the ongoing work. What I want to do is to make that change in the master table and have it cascade to the appropriate related table. Currently it must be changed in the related tables. There are additional related tables for tracking the purchasing, specification, shipping status, etc, which also need to be cascade updated, although those functions have not yet been developed. If I can get the referential integrity and cascade update working then I won't have to right additional code to handle the process.

Thanks for the interest.

Alan

[orange]

Please tell us about your tables and relationships.
Can you post a jpg of your relationships window?

Have you tested your data model with some sample data and scenarios?

[June7]

I avoid compound keys as much as possible. I would prefer to use autonumber as the key. Can still set the multiple fields as a compound index to prevent duplicate combinations.

Generating custom unique identifier is a common topic.

If you want to use that value for sorting, be aware that numbers will be subject to alpha sort rules. So 101 will sort before 20. Need placeholder zeros. So 0101 will sort after 0020.

[ButtonMoon]

acannon,
Sounds fine so far, but what is your question? To enable cascading updates using a composite unique key just select the "Enforce Referential Integrity" and "Cascade Update Related Fields" options.

[ssanfu]

I have 12 subsidiary tables to relate to 1 primary table.

OK, this is how I would structure the main table

Main Table
------------
MainID_PK Autonumber (PK)
txtAAA Text
txtTTT Text
intSSSS Integer
intDD Integer
bolDeleted Yes/No (boolean)
...other fields....

I added the field "bolDeleted"; really shouldn't have one field for two items (part of the equipment number and if it is deleted - this violates normalization rules).

I would set a multi-field index using the fields "txtAAA, txtTTT, intSSSS, intDD" set to "INDEXED NO DUPLICATES".

The 12 subsidiary tables would be related to the Master table using MainID_PK. I usually name the foreign key field like "MainID_FK". No need for cascading updates.

As orange requested, I also would like to see a jpg of your relationships window to have a better understanding of your tables/relationships.

[acannon]

My problem has been solved. The dual-key relationship worked exactly as needed, including the cascade update and delete.