referential integrity problem

[aidan]

Basically I have a table Customers and a table Accounts. #customer is the sole primary key of Customers, and #account+#customer is the composite key of Accounts.

A customer cannot create a new account without being a customer, and so normally I would enforce referential integrity and in access it would appear as a one to many relationship.

However, according to my boss, in rare circumstances, an entry in Customers can be deleted, and the orphaned accounts associated with a new #customer.

So at the moment I have it set up how I would have, only without referential integrity, but I don't think this is going to work properly for three main reasons:

1) The address in Customers needs to be displayed in the accounts form.

2) The #customer part of the composite key would need to be changed somehow.

3) There is obviously no referential integrity, and there are too many customers to limit accounts.#customer to a combo.

Note that I've made this simpler than it looks - the customer table is connected to other repeating groups too.

I think my question is whether this is a common problem, and if so is there a common solution to it?

Aidan

 

[FoFa]

You can setup referential Intgrity to account for the link, but not enforce cascade deletes. This basically will not allow a master record to be deleted unless there are no child records attached to the master record. That solves one problem.
Address should not be part of the account table. You can still show the customers address by the tie back to the customer, change your application to use it in that way.
You do not need a composite key, a foreign key would work. If you wish to keep it as a composite key, you should then create a process to "move" the child records to a new customer. In theory this would be an Insert of the new (copied) record and a delete of the old record. How ever if you use a foreign key to point back to the customer, you can just change the foreign key instead.

 

[aidan]

I think you're right about the composite key. I'm not sure why my boss wanted that honestly.

However the referential integrity thing you mentioned wouldn't work - he wanted to be able to delete the parent table, leaving the child accounts in limbo, but I just received a reply from him and he's not asking for this anymore.

Thanks for such a quick reply though,

Aidan