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?