Re: SQL Contraints

Patrick Lanphier wrote:
>
> I am wondering the best way to solve this problem. I would like to do
> the following generate a unique id between account.account_number and
> groups.group_id that would then be used to join in one migrated foreign
> key to addresses.lookup_id. Would the below SQL work properly? If so
> do you know how to get ERwin to do this?

Reverse the relationship. Make the address table the parent to both accounts and groups, or add a fourth table that will be parent to all, a master to enforce the relationship. I'll take the first suggestion as simplest. Since I assume every group or account has an address this works fine. For any new group or account add an address record, the lookup_id becomes the primary key and a serial number or sequence or whatever your server supports for sequential automatic integers, and the account_number and group_id become foreign keys referencing the address table as well as primary keys to themselves. This properly enforces referential integrity. If you need cascade delete from the account/group side of the relationship it will have to be implemented in a trigger while if your server supports it cascade delete from the address table side is no problem.

The second suggestion of a master record, parent to the three existing tables, is sound if there is common data between group and account that you can move there, otherwise the table would be a gerund, and unneccessary since address is a perfectly good master record as it is.

Art S. Kagel Received on Wed May 19 1999 - 12:08:52 CDT