Foreign Key + Unique Constraint

Hey,

I went through the latest documentation (19.1.x) for Foreign keys and a few things are unclear.

CREATE TABLE IF NOT EXISTS customer_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    mobile_number STRING NOT NULL,
    email_address STRING NOT NULL,
    active STRING (1) NOT NULL CHECK (active IN ('Y',
        'N')) DEFAULT 'N',
    UNIQUE (mobile_number, email_address, active)
);

CREATE TABLE IF NOT EXISTS sms_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    mobile_number STRING NOT NULL REFERENCES customer_test (mobile_number) ON DELETE CASCADE
);

Generates this error:

pq: there is no unique constraint matching given keys for referenced table customer_test

The requirement is that the combination of mobile_number, email_address and active is UNIQUE but also have the mobile_number and email_address columns be referenced in other tables.

What is the best way to solve this?

Hi @batman,

The foreign key constraint on sms_test.mobile_number (the referencing column) requires that customer_test.mobile_number (the referenced column) contains only unique values. However, the UNIQUE constraint in customer_test guarantees that each combination of mobile_number, email_address, and active is unique, not that each value in mobile_number is unique.

If you add an additional UNIQUE constraint just on customer_test.mobile_number, this will work as expected:


CREATE TABLE IF NOT EXISTS customer_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    mobile_number STRING NOT NULL UNIQUE,
    email_address STRING NOT NULL,
    active STRING (1) NOT NULL CHECK (active IN ('Y', 'N')) DEFAULT 'N',
    UNIQUE (mobile_number, email_address, active)
);

CREATE TABLE IF NOT EXISTS sms_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    mobile_number STRING NOT NULL REFERENCES customer_test (mobile_number) ON DELETE CASCADE
);

Does that help?

Best,

Jesse

Thanks for the reply.

The requirement is that mobile_number and email_address uniqueness applies only if customer has an active account.

If I set the UNIQUE constraint on mobile_number it implies that if the customer closes their account (active = ‘N’) and if they later sign up again with a same mobile number but different email address, the UNIQUE constraint on the mobile_number will prevent them from being created again as a new row.

Ah, thanks for the clarification on that business requirement. My understanding is that a referenced column must contain unique values and so must use a UNIQUE constraint or be the PRIMARY KEY. This is also stated in our docs. I’m not sure how to accomplish what you want given that you have a multi-column UNIQUE constraint. I’ll ask for ideas from our SQL devs.

Hi @batman,

The requirement is that the combination of mobile_number, email_address and active is UNIQUE but also have the mobile_number and email_address columns be referenced in other tables.

It looks like, if you can’t apply the UNIQUE constraint to mobile_number and email_address, you’ll have to find some other way to meet your requirement.

One idea is to make those columns unique and when you set active to N, also null out those columns, maybe copying the values to some other fields like mobile_number_deleted or email_address_deleted.

Another approach would be to implement this logic in your app by, for example, counting active rows for a given mobile_number.

Yet another would be to not foreign key to these columns at all but rather to a unique id.

Does any of that help?

Best,
Jesse

@jesse, Thanks for taking the time to check and respond.

The way I will solve this for now is to keep the unique constraint across those 3 columns but not make mobile_number and email_address columns unique. I’ll use the customer.id as foreign key in the sms_test table.

CREATE TABLE IF NOT EXISTS customer_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    mobile_number STRING NOT NULL,
    email_address STRING NOT NULL,
    active STRING (1) NOT NULL CHECK (active IN ('Y',
        'N')) DEFAULT 'N',
    UNIQUE (mobile_number, email_address, active)
);

CREATE TABLE IF NOT EXISTS sms_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    customer_id UUID NOT NULL REFERENCES customer_test (id) ON DELETE CASCADE,
    mobile_number STRING NOT NULL
);