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
NOTNULL     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,
addrTEXT
 
);
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
addressentry for        -- it exists.        PERFORM 1 FROM address2 WHERE customer2_id = $1 LIMIT 1;        IF NOT
FOUNDTHEN            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