Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

> For a single relation, obviously there is no difference. But at the
> database level there is at least one case - composite overlapping
> keys

I have lots of those in the schema that I use. The entities are in fact arranged into a deeply nested hierarchy (which corresponds very well to the semantics of the application domain), where the (natural) keys of "child" entities include foreign keys to (often several different) "parent" entities. With lots of overlapping.

Due to the level of depth of the nesting, primary keys, especially for n:m(:o:...) tables can be long - I stopped counting at 15 columns or so.

> - when a surrogate key design can lead to anomalies if not
> supplemented by other restrictions.

That's exactly the kind of application case I had in mind.

Lots of tables with lots of foreign key constraints, often several of them in one primary key of a "child" entity.  

> Maybe is not the best example, but let’s take the following database
> (with the obvious foreign keys):
>
> clients {client}
> client_orders {client, order}
> client_locations {client, location}
>
> deliveries {delivery_no, client, order, location}
>
> Now add surrogate keys to all relations, and replace the last one
> with:
>
> deliveries {delivery_no, order_id, location_id}
>
> You may see that client attribute is no longer needed since it can be
> determined from either order_id or location_id. However this design
> no longer enforce the rule that the order and the location should
> refer to the same client.

Thanks a *lot* for that one. And even if you do include the client_id in deliveries (as the standard suggestion would probably be), that still won't enforce the consistency here. And I wouldn't even *want to* try and investigate what constraints I would need to enforce integrity in my case, given the shear number of entities and the complexity of the dependencies between the primary/foreign keys.

Since I couldn't find an example like that in the (online as well as printed) literature, is there a textbook (or online documentation) about these issues? First, to quote it when I have to argue with people, and second, to improve my own education.

Sincerely,

Wolfgang Received on Mon Mar 11 2013 - 19:53:56 CET