Referential Integrity (1 Viewer)

[k209310]

Counld somebody please explain to me referntial integrity and how it works in access or a least point me in the direction of somewhere that explains it better than Access Help.

Im pretty sure i need this to keep my records in my tables up to date however when i to turn it on in the relationship window i get an error message telling me that there is no unique value in the Primary tabkle. By this i am assuming it means a primary key. (am i correnct in doing this). I do have a primary key in both table. I have three relationships in the table. One of the relationships invovlves the primary key in the first table.

I hope this makes sense any help/advice is greatly recieved.

 

[Robert Dunstan]

Hi there

Basically 'Referential Integrity' is a way of checking that the relationship between 2 tables is valid. Take the following example:

You raise a purchase order to a supplier, the relationship between them is that one supplier can have many orders but one order can only have one supplier. Therefore it is a one to many relationship.

In this example lets say you have 2 tables : tblSupplier and tblOrders. In tblSuppliers you will have SupplierID (Primary Key) and in tblOrders you will have OrderID (Primary Key) and SupplierID (Foreign Key) which is the linking field to tblSuppliers. Therefore the relationship is between the SupplierID fields. If referential integrity is enforced this would mean that you would not be able to delete a Supplier from tblSuppliers if it had a related record (i.e. an order) in tblOrders. And likewise you would be not able to add an order record in tblOrders if the related supplier record in tblSuppliers was NOT in existence.

It's not easy to understand if you're unsure about relationships in general but if you're happy that your table designs are correct then it's always advisable to enforce referential integrity to maintain the stability of your related data.

HTH

Rob

 

[k209310]

Thanks for the reply rob. Its basically what i thought. SO thanks for clearing that up.

To set up relationships with referential integrity do the record therefore have to be unique? I have been trying to set up these kind of relationships however i have been having trouble with an error message telling me that i have no unique index. Does this mean that the record has to be unique?

Thanks for reply tho

Chris

 

[Robert Dunstan]

Chris,

If you want to enforce referential integrity on a one to many relationship then the record on the 'one' side must have a unique key like an 'ID' number that uniquely identifies that record. If you set this field as the primary key then also make sure that the 'Indexed' property is set to 'Yes - No Duplicates'

For the 'many side' (the foreign key) you can have the index property set 'Yes - Duplicates OK'

In answer to your question 'Does the record have to be unique?'
Well no not exactly you could have 2 records in a table that are exactly the same e.g. a table of employee names. In this scenario you might well have 2 or more employees with the same name. However they are uniquely identified in the table by way of an ID field which becomes your primary key and is indexed but with no duplicates.

Clear as muddy water!!

HTH
Rob

 

[k209310]

Thaks once again for the reply. Thats a lot clearer now and im beginning to get somewhere. I guess its the same as any other IT development. It just takes a bit of practice.

Hopefully in a couple of weeks i will look back and laugh at how a got stuck on such a simple task - Hopefully.

Thank you once again

Chris

 

[Robert Dunstan]

No probs, glad I could help.