Re: Deferrable NOT NULL REFERENCES ... for two-way referential relationship (SOLVED?)

Craig Ringer wrote:
> Hi all
>
> I'm running into an issue with my schema where I'm trying to establish a
> mandatory two-way relationship between two entities, and I'm hoping for
> some external wisdom. Using "customer" and "address" by way of example:
>
> CREATE TABLE customer (
> id INTEGER PRIMARY KEY,
> address_id INTEGER NOT NULL
> REFERENCES address(id) DEFERRABLE INITIALLY DEFERRED
> )
>
> CREATE TABLE address (
> id INTEGER PRIMARY KEY,
> customer_id INTEGER NOT NULL
> REFERENCES customer(id)
> )

OK, it looks like there are at least two ways to do it, and I'm hoping
for some comments from the experts on what seems sanest/cleanest/most
future proof and the best in a concurrent environment I also figure this
post might be useful for the archives.

It looks like it's possible to avoid the use of a constraint trigger by
making address_id in the above NOT NULL and inserting a dummy value when
inserting the customer record. A DEFAULT(-1) clause on address_id will
have much the same effect of a deferred NOT NULL check when combined
with the deferred REFERENCES check.

An AFTER ... INSERT trigger on address then updates the associated
customer with the address_id, and an AFTER ... DELETE trigger ensures
that if an address is deleted another valid ID (if any exists, otherwise
null) is inserted into customer.address_id.

However, as above the schema will permit customer to reference an ID
that doesn't have a reference back to the same customer, so it'd have to
be something like:

CREATE TABLE customer (
id SERIAL PRIMARY KEY,
address_id INTEGER NOT NULL DEFAULT(-1),
name TEXT
);

CREATE TABLE address (
id SERIAL PRIMARY KEY,
CONSTRAINT address_pkey_not_negative CHECK(id >= 0),
customer_id INTEGER NOT NULL
REFERENCES customer(id) ON DELETE CASCADE,
addr TEXT,
UNIQUE(id, customer_id)
);

ALTER TABLE customer ADD CONSTRAINT customer_address_fkey
FOREIGN KEY (address_id, id)
REFERENCES address(id, customer_id) DEFERRABLE INITIALLY DEFERRED;

CREATE OR REPLACE FUNCTION address_insert_trig_set_cust_address_id ()
RETURNS trigger AS $$
BEGIN
UPDATE customer
SET address_id = new.id
WHERE customer.id = new.customer_id
AND customer.address_id = -1;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION address_delete_set_cust_address_id ()
RETURNS trigger AS $$
BEGIN
UPDATE customer
SET address_id =
(SELECT id
FROM address
WHERE address.customer_id = customer.id
LIMIT 1)
WHERE customer.id = old.customer_id;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER address_insert_cust_address_id AFTER INSERT ON address
FOR EACH ROW EXECUTE PROCEDURE address_insert_trig_set_cust_address_id();

CREATE TRIGGER address_delete_cust_address_id AFTER DELETE ON address
FOR EACH ROW EXECUTE PROCEDURE address_delete_set_cust_address_id();

An UPDATE trigger preventing changes to the id field of address is assumed.

As a result, the whole thing is transparent to users, though it does
mean that inserts/deletes to `address' result in a lock on the
associated customer record. I'm not 100% sure it's free from races or
locking issues, but at least with races in this case the worst outcome
should be an unexpected error on commit, right?

It works, though. The error from a delete removing the last record could
be nicer, but that's easily fixed in the DELETE trigger.

Does this look like a better idea than just using a deferred constraint
trigger (on both customer and address, to catch customers inserted w/o
an address and to catch deleted addresses) to directly verify that at
commit time a customer has one or more addresses?

I guess a deferred constraint trigger for the same job, again assuming
an UPDATE trigger is in place to prevent changes to address2.id and
customer2.id, would look like:

CREATE TABLE customer2 (
id SERIAL PRIMARY KEY,
name TEXT
);

CREATE TABLE address2 (
id SERIAL PRIMARY KEY,
customer2_id INTEGER REFERENCES customer2(id) ON DELETE CASCADE,
addr TEXT
);
CREATE INDEX address2_customer2id_idx ON address2(customer2_id);

CREATE OR REPLACE FUNCTION assert_customer_has_address(INTEGER)
RETURNS VOID AS $$
BEGIN
-- Make sure the customer record actually exists; if it does
-- not then consider the assertion to be passed.
IF EXISTS (SELECT 1 FROM customer2 WHERE id = $1) THEN
-- Customer exists. Verify that at least one address entry for
-- it exists.
PERFORM 1 FROM address2 WHERE customer2_id = $1 LIMIT 1;
IF NOT FOUND THEN
RAISE EXCEPTION 'Customer must have one or more related
address entries';
END IF;
END IF;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION customer_inserted_addrcheck_tr()
RETURNS TRIGGER AS $$
BEGIN
PERFORM assert_customer_has_address(new.id);
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION address_deleted_addrcheck_tr()
RETURNS TRIGGER AS $$
BEGIN
PERFORM assert_customer_has_address(old.customer2_id);
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE CONSTRAINT TRIGGER address_deleted_addrcheck
AFTER DELETE ON address2 DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE address_deleted_addrcheck_tr();

CREATE CONSTRAINT TRIGGER customer_inserted_addrcheck
AFTER INSERT ON customer2 DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE customer_inserted_addrcheck_tr();

Any opinions on the best approach? At the moment I'm inclined to favour
the constraint trigger (no "dummy" field in customer; no spurious update
of customer on address change; etc) but I know way too little about this
so I'd be very interested in comments.

--
Craig Ringer