Change primary key from simple to composite with new column

Question

I am trying to change the primary key of one of my tables from a simple to a composite key, where the composite should be composed of the old pk column and a newly created one.

I followed along this answer from a very similar question: https://stackoverflow.com/a/27832197/1948454

It almost works, except that there is no value set in the dependant table for the new column.

Here's the situation:

Suppose I have a table for a catalog, and a table for catalog entries. Before:

-- DDL Catalog

CREATE TABLE public.Catalog (
    name_ VARCHAR(255) NOT NULL,
    foo_ VARCHAR(255) NULL,
    CONSTRAINT Catalog_pkey PRIMARY KEY (name_)
);

-- DDL CatalogEntry

CREATE TABLE public.CatalogEntry (
    pricekey_ VARCHAR(255) NOT NULL,
    pricekeyroot_ VARCHAR(255) NOT NULL,
    catalog_name_ VARCHAR(255) NULL,
    bar_ VARCHAR(255) NULL,
    CONSTRAINT CatalogEntry_pkey PRIMARY KEY (pricekey_, pricekeyroot_)
);    

-- public.CatalogEntry FOREIGN KEYs

ALTER TABLE public.CatalogEntry ADD CONSTRAINT CatalogEntry_catalog_name__fkey FOREIGN KEY (catalog_name_) REFERENCES Catalog(name_) ON DELETE CASCADE;

So CatalogEntry.catalog_name references to Catalog.name_.

Now I need to add another column version_ to the Catalog table, indicating a version of some catalog. This means I will have to create a new composite pk composed of name_ and version_. Here's my script:

-- UPDATE script

-- add the new version column and set all values to default of 1
ALTER TABLE Catalog ADD version_ INTEGER;
UPDATE Catalog SET version_ = 1;
ALTER TABLE Catalog ALTER column version_ SET not null;

-- update primary key and foreign key
BEGIN;
-- first, drop fkey constraint on CatalogEntry
ALTER TABLE CatalogEntry DROP CONSTRAINT CatalogEntry_catalog_name__fkey;
-- then, update Catalog primary key
ALTER TABLE Catalog DROP CONSTRAINT Catalog_pkey,
    ADD CONSTRAINT Catalog_uni_name UNIQUE (name_),
    ADD PRIMARY KEY (name_, version_);
-- now add new foreign key again to CatalogEntry
ALTER TABLE CatalogEntry ADD catalog_version_ INTEGER;
ALTER TABLE CatalogEntry 
    ADD CONSTRAINT CatalogEntry_catalog_name__catalog__fkey FOREIGN KEY (catalog_name_, catalog_version_) 
        references Catalog(name_, version_ ) ON DELETE CASCADE;
COMMIT;

-- finally, remove unique constraint on name since it is not needed anymore
ALTER TABLE Catalog DROP CONSTRAINT Catalog_uni_name;

After performing these steps, the primary and foreign key appear to be set correctly - but the value of CatalogEntry.catalog_version_ is null. The corresponding value of Catalog.version_ is set correctly to 1.

Where is my mistake? Do I also have to set CatalogEntry.catalog_version_ manually to 1? I would have assumed that it would be set automatically.

score 1 · Accepted Answer · answered Jun 17 '20 at 10:19

The value of CatalogEntry.catalog_version_ doesn't magically get set just because you define a foreign key constraint.

What effectively happened is that no row in CatalogEntry references a row in Catalog. The reason is that the default for foreign key constraints is MATCH SIMPLE, see the documentation:

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

You should create the foreign key constraint as MATCH FULL so that either all or none of the columns must be NULL. Then you would have received an error creating the foreign key.

Solution: update CatalogEntry and set the column to 1 there as well, then define the foreign key with MATCH FULL.

Thanks a lot for the explanation and solution. – codebat Jun 17 '20 at 11:06 — codebat, Jun 17 '20 at 11:06

Change primary key from simple to composite with new column

1 Answers1