Here is one of the tables that can have millions of rows and foreign key
constraints to 5 other tables.
CREATE TABLE public.work_session
(
work_session_id integer NOT NULL DEFAULT
nextval('worksession_worksessionid_seq'::regclass),
customer_id integer NOT NULL,
store_id integer NOT NULL,
department_id integer NOT NULL,
station_id integer NOT NULL,
start_date_time timestamp(6) without time zone NOT NULL,
end_date_time timestamp(6) without time zone NOT NULL,
job_id integer NOT NULL,
goal smallint NOT NULL,
employee_count smallint NOT NULL DEFAULT 1,
pieces integer NOT NULL,
end_reason_id integer,
piece_counter_session_id integer,
work_session_guid uuid,
CONSTRAINT worksession_pkey PRIMARY KEY (work_session_id, customer_id),
CONSTRAINT fk_worksession_department FOREIGN KEY (department_id, customer_id)
REFERENCES public.department (department_id, customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_worksession_endreason FOREIGN KEY (end_reason_id, customer_id)
REFERENCES public.end_reason (end_reason_id, customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_worksession_job FOREIGN KEY (job_id, customer_id)
REFERENCES public.job (job_id, customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_worksession_station FOREIGN KEY (station_id, customer_id)
REFERENCES public.station (station_id, customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_worksession_store FOREIGN KEY (store_id, customer_id)
REFERENCES public.store (store_id, customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT chk_worksession_employeecount CHECK (employee_count > 0)
)
(indexes on each fk set of columns removed for brevity)
Here is the first related table.
CREATE TABLE public.department
(
department_id integer NOT NULL DEFAULT
nextval('department_departmentid_seq'::regclass),
customer_id integer NOT NULL,
department_name citext NOT NULL,
track_redos boolean NOT NULL,
reset_redos boolean NOT NULL,
CONSTRAINT department_pkey PRIMARY KEY (department_id, customer_id),
CONSTRAINT fk_department_customer FOREIGN KEY (customer_id)
REFERENCES public.customer (customer_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT unq_department_customerid_departmentname UNIQUE (customer_id,
department_name)
)
Thanks,
Craig
From: David G. Johnston [mailto:david.g.johns...@gmail.com]
Sent: Monday, August 8, 2016 11:33 AM
To: Craig Boucher <cr...@wesvic.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Column order in multi column primary key
On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher <cr...@wesvic.com
<mailto:cr...@wesvic.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.
