Add new PKs to Postgres tables
Closed, ResolvedPublic
Actions

Assigned To

None

Authored By

	Reedy
	Aug 29 2017, 5:58 PM

Description

Forked from T172514 where various UNIQUE indexes were changed to proper Primary Keys, changes to Postgres should be made to keep the schema in line

In T172514#3502096, @Reedy wrote:
For PostgreSQL...

Tables that already have a PK:
log_search
text (or rather pagecontent)
querycache_info.qci_type, site_stats.ss_row_id, transcache.tc_url have type UNIQUE

From https://www.postgresql.org/docs/8.1/static/ddl-constraints.html
Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint. So, the following two table definitions accept the same data:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
So site_stats.ss_row_id and transcache.tc_url are effectively a PK, with slightly different syntax

MySQL changes done in https://gerrit.wikimedia.org/r/#/c/370190/13 were:

ALTER TABLE /*_*/categorylinks DROP KEY /*i*/cl_from, ADD PRIMARY KEY (cl_from,cl_to);
ALTER TABLE /*_*/templatelinks DROP INDEX /*i*/tl_from, ADD PRIMARY KEY (tl_from,tl_namespace,tl_title);
ALTER TABLE /*_*/pagelinks DROP INDEX /*i*/pl_from, ADD PRIMARY KEY (pl_from,pl_namespace,pl_title);
ALTER TABLE /*_*/text DROP KEY /*i*/old_id, ADD PRIMARY KEY (old_id);
ALTER TABLE /*_*/imagelinks DROP KEY /*i*/il_from, ADD PRIMARY KEY (il_from,il_to);
ALTER TABLE /*_*/iwlinks DROP KEY /*i*/iwl_from, ADD PRIMARY KEY (iwl_from,iwl_prefix,iwl_title);
ALTER TABLE /*_*/langlinks DROP KEY /*i*/ll_from, ADD PRIMARY KEY (ll_from,ll_lang);
ALTER TABLE /*_*/log_search DROP KEY /*i*/ls_field_val, ADD PRIMARY KEY (ls_field,ls_value,ls_log_id);
ALTER TABLE /*_*/module_deps DROP KEY /*i*/md_module_skin, ADD PRIMARY KEY (md_module,md_skin);
ALTER TABLE /*_*/objectcache DROP KEY /*i*/keyname, ADD PRIMARY KEY (keyname);
ALTER TABLE /*_*/querycache_info DROP KEY /*i*/qci_type, ADD PRIMARY KEY (qci_type);
ALTER TABLE /*_*/site_stats DROP KEY /*i*/ss_row_id, ADD PRIMARY KEY (ss_row_id);
ALTER TABLE /*_*/transcache DROP KEY /*i*/tc_url_idx, ADD PRIMARY KEY (tc_url);
ALTER TABLE /*_*/user_former_groups DROP KEY /*i*/ufg_user_group, ADD PRIMARY KEY (ufg_user,ufg_group);
ALTER TABLE /*_*/user_properties DROP KEY /*i*/user_properties_user_property, ADD PRIMARY KEY (up_user,up_property);