Re: Column order in multi column primary key
| От | Craig Boucher |
|---|---|
| Тема | Re: Column order in multi column primary key |
| Дата | |
| Msg-id | 0d1d01d1f1a8$086c4c90$1944e5b0$@wesvic.com обсуждение исходный текст |
| Ответ на | Re: Column order in multi column primary key ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: Column order in multi column primary key
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-general |
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.johnston@gmail.com]
Sent: Monday, August 8, 2016 11:33 AM
To: Craig Boucher <craig@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 <craig@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.
В списке pgsql-general по дате отправления: