I am designated emigrate a database to some mid-class ERP. The brand new system uses composite primary secrets in some places, and from the practical perspective, why?

In comparison to autogenerated IDs, I'm able to only see negative aspects

  • Foreign secrets becomes fuzzy
  • Harder migration or db-redesigns
  • Inflexible as business change. (My vehicle doesn't have reg.plate..)
  • Same integrity better accomplished with constraints.

It's falling to the look idea of candiate secrets, that we neither see the purpose of.

Could it be a habit/artifact in the floppy-days (reducing space/indexes), or am I missing something?

//edit// Just found good SO-publish: Composite primary secrets versus unique object ID area //

Composite secrets are needed whenever your primary secrets are non-surrogate and naturally, um, composite, that's, breakable into several non-related parts.

Some real-world good examples:

  • Many-to-many link tables, where the primary secrets are comprised from the secrets from the organizations related.

  • Multi-tenant programs when tenant_id is part of primary key of every entity and also the organizations are just linkable inside the same tenant (restricted with a foreign key).

  • Programs processing third-party data (with already provided primary secrets)

Observe that realistically, all this is often accomplished utilizing a UNIQUE constraint (additional to some surrogate PRIMARY KEY).

However, you will find some implementation specific things:

  • Some systems will not let a FOREIGN KEY make reference to anything that's not really a PRIMARY KEY.

  • Some systems would only cluster a table on the PRIMARY KEY, hence making the composite the PRIMARY KEY would improve performance from the queries joining around the composite.

Composite primary key provides better performance if this involves them getting used as Foreign secrets in other tables and reduces table reads - sometimes they may be existence savers. If you are using surrogate secrets, you must see that table to obtain natural key information.

For instance (pure example - so we're not speaking DB design here), allows if you have an ORDER table and ORDERITEM. If you are using ProductId and LineNumber (UPDATE: so that as Pedro pointed out OrderId as well as OrderNumber) as composite primary type in ORDERITEM, then inside your mix table for SHIPPING, you'd have the ability to have ProductId within the SHIPPING_ORDERITEM. This could massively improve your performance if for instance you've exhaust that product and want to discover all items of this ProductId that should be shipped with no have to join.

However, if you are using a surrogate key, you need to join and also you finish track of a really inefficient SQL execution plan where it needs to do bookmark research on several indexes.

See more on bookmark research which using surrogate secrets turns into a major problem.