Database

Anon

I would like to know why the concept of enforcing referential integrity in Access? why is this so important?

Thank you.

ccranium

Referential integrity's a bit of an involved subject to get into on a bulletin board, but primarily it's there so that only valid data can be entered into the database.

Lets say you're working with a chain of stores, so you have a table called stores, with intStoreId as the Primary Key. Each store has it's own id.

Now you have an inventory table that all the stores access, so if a customer comes in and says "Do you have Product X", the salesperson can go the computer, look it up and say "No, I'm sorry, but Store Y has one."

Your inventory table may contain the Product ID (presumably from a product table), the Quantity, and the Store ID. The Product ID and the Store ID combine to make a joint key.

This all comes back to referential integrity when you're adding new information into the inventory table. By enforcing the foreign keys (in the inventory table the Product ID and Store ID are both foreign keys) the database management software (in your case Access) ensure that only products that already exist in the product table are added, and they're only listed against stores that already exist.

So the referential integrity makes it impossible to add products that don't exist, or (and in an inventory scenario probably a worse situation) adding a product to the inventory of a store that doesn't exist.

Does that make things any clearer, or did I just make things even more confusing?

Anon

OK. Let's say you call up your Visa company, and pay your bill by phone. Your bank processes the payment, subtracting the amount of the payment from your account. As this happens, a glitch in the processing forgets to put this as a payment against the Visa account.

Your account shows overdue, you owe money you thought you had paid, and your account is short, but since there's no referential integrity, no one can figure out where the money went.

Or, let's say that you're entering data for heart transplant lists. A heart goes into the list, but doesn't have a key pointer to who it came from and it's data type. I.e. it's an orphan record. now you've got a red, beating heart, and no idea who is a good match for it.

Or, maybe you're leasing cars, and somehow the same car winds up in the database twice. Now you try to lease it to a second customer. He comes to pick it up. It's not on the lot.

All of these are examples of things that happen when your database has no referential integrity.