Re: Column order in multi column primary key

On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher <craig(at)wesvic(dot)com> wrote:

> PG 9.5
>
>
>
> I’m in the process of converting our application from Sql Server to
> Postgresql. I’m taking advantage of this process to make some database
> design changes.
>
>
>
> Our database contains data for many customers and I have a customer_id
> column in every customer related table. One of the changes I made was to
> change all of the tables that use an auto incrementing number as the
> primary key to also include a customer_id in the pk. I also changed all of
> the foreign keys to include both the id number column and the customer_id.
> I made this change so when inserting a child record, I don’t have to look
> up each parent record to verify that it is owned by that customer. The
> database handles this check for me now through foreign key constraints. My
> question about multi column primary keys is should I have the customer_id
> column first (which will have many repeated rows) and then the auto
> incrementing id field (which will most likely be unique in the table), or
> should it be there other way around. Will the pk index perform better one
> way or the other or will it no matter?
>
>
>
> The number of customers are in the hundreds and the number of child
> records in some of the tables can be in the millions.
>
>
>
​The PK should be (child, parent) - selectivity is the most important
aspect of the PK.

You will also want an index on just (parent) to support its FK nature.​

But, your description seems lacking...I'd suggest you show an example set
of tables with names, PKs and FKs

David J.