Enforce referential integrity

[zoeknowles]

Hi all,

Can anyone help please? I am trying to create a relationship between two fields of two tables, and enforce referential integrity (I am trying to get ticks in all three check boxes, including cascading updates and cascading deletes in the edit relationships window). The problem is it won't let me. When I click on 'create', it gives me an error message 'Invalid field definition 'costume item ID' in definition of index or relationship.

I have attached the database to this post. I am trying to link the tables 'costume item' and 'JT3 costume items to students', via the fields 'costume item ID'.

Can anyone let me know what I'm doing wrong?

Thanks
Zoe

 

[GaryPanic]

couldn't open your b/b
but looking at the brief

I think you need both fields to be the same type (and I think the type has to be numbers )

 

[zoeknowles]

Thanks for that Gary. The field in one of my tables is specified as a 'Number' long integer, and the field in the other table it is specified as an 'Autonumber' long integer. Is this what is causing problems? If so what should I be setting them to?

...and what is b/b? You'll have to excuse me I'm new to all of this!

Thanks again
Zoe

 

[GaryPanic]

oops typo
d/b (database)
others
FE/BE
Front end / Back end

 

[redneckgeek]

Thanks for that Gary. The field in one of my tables is specified as a 'Number' long integer, and the field in the other table it is specified as an 'Autonumber' long integer. Is this what is causing problems? If so what should I be setting them to?

Seems to be set up properly. I was able to open the attachament, and I linked the tables together without getting an error message. Try doing a compact and repair on the version you have, and then try linking them again.

 

[gemma-the-husky]

to do what you are doing, the table with the autonumber will become (should be) a master reference to the other (lets call it slave) table

so that you can have items in the master (autonumber) table that may not be in the other (slave) table, but not the other way around. The referential integrity therefore enforces a 1 to many link, from the master to the slave table

so if at the moment, in the slave table you have items that do not exist in the master table, access will not enable you to create a relationship with referential integrity, as your tables are breaking that relationship

its the same issue as trying to create a unique index on a column that isnt all unique

------
the other point is that both fields have to be of the same type, but an autonumber is a long, so that doesnt appear ot be an issue

i am not sure offhand, i think it is possible that both tables must need to have a primary key also, and that the referential integrity must use the primary key from one of the tables - not 100% sure about this, but I think this would be logical, captain. So although you have an autonumber, if it isnt the primary key, this may be an issue.

 

[Rabbie]

i am not sure offhand, i think it is possible that both tables must need to have a primary key also, and that the referential integrity must use the primary key from one of the tables - not 100% sure about this, but I think this would be logical, captain. So although you have an autonumber, if it isnt the primary key, this may be an issue.

You shouldn't have an autonumber field unless it is the primary key. As many posts on this forum have stated an autonumber should only be used to generate a unique number for a key. It should not have any other significance. It only make sense with referential integrity to use the PK key from the master table. It is not necessary for the slave table to have a primary key tho' this is usually a good thing.

 

[tokoloshi]

trying to link the tables 'costume item' and 'JT3 costume items to students', via the fields 'costume item ID'

Zoe, I have included just the relevant bits of the relationship diagram as a quick point of reference.

The problems are as follows:

1. Master Table has PF manually set

The Master Table (costume item) has a Primary Key set, but you are expecting to manually input a unique value in here. By manually I imply that you will take responsibility for how this record is uniquely identified instead of letting the DBMS do this.
The problem here is that your DBMS (Access in this case) knows that it needs to relinquish control to you from the first record that you create onwards. Now, since this is a MASTER table, ie no children records in linked tables can be created without there being a record in this table, it means that your DBMS has to relinquish control over how you are going to enforce referential integrity from this point on.

Not exactly what you had intended, but this is the effect of specifying to the DBMS that you will control the identity of the Master table.

You could have gotten away with this if you had to link this to a unique single column/field in the child table, but this is where the second problem raises its ugly little head.

2. Incorrect Unique Identifier in Child Table

The Child/Linked table (JT3 costume items to students) is obviously a "mapping" or FACT table that contains references to at least 4 other tables that unique identifies a specific fact about a given transaction.

In this table you are wanting to keep track of several events that would under other circumstances represent a many-to-many relationship. What this table is doing is keeping track of a given situation where a given student has been issued with a given costume of a given costumer type for a given dance.

You are not keeping track of a couple of other key values that would uniquely identify this specific transaction. Questions like:

• When did this specific event occur? Shouldn't you have some date related data here?
• Is the costume returned yet? Shouldn't you have a Yes/No RETURNED field in here?
• What was the cost of this transaction and has a deposited been taken for this?

There are probably other thoughts that could also have been captured into this table. By the same token, keeping track of the costumer typeis maybe not necessary here since you can get that information from the Costume Item table, unless you are thinking that some constumes, for a given transaction may be registered as a different costume type?

The reason for these observations is simply that the set of columns together will uniquely identify this record, but there is no need to hold all of the fields together as a primary key. It is far more efficient to allow your DBMS to create the unique identifier on the fly than to undertake this responsibility yourself.

This is obviously something that you had realised, hence the creation of the AUTONUMBER column in the child table and some of these tracking questions in the Master table.

The problem is that this table needed its own Unique Identifier column, which is set as the PK and it needed to be an autonumber field. The Costume ID should then have been drawn down from the Costumer Item table, where it would originally have been created.

The question you should be constantly asking yourself when designing the solution is:

1. What does a Costumer Type have a lot of?
   1. A: Costumes. There you have a Costumer Type table which will have a PK with an Autonum field. The same field will be reflected in a Costume table as a field type of number.
   2. The Costume table will now also have its own PK field that is an Autonum type of field.
2. What does a Costume have a lot of?
   1. A: A lot of transactions that reflect when it was taken out, how much was paid and whether or not a deposit was taken - hence your JT3 table that would have its own PK/Autonum field plus a field called CostumeID that points back at the Costume table and is a Number type of field.

You have to follow through on that thought process throughout your design.

I have amended portion of you design for the sake of clarity and uploaded an image of the end result here also for you convenience.

Hope this helps...