Re: Deferrable NOT NULL REFERENCES ... for two-way referential relationship (SOLVED?) - Mailing list pgsql-sql
From | Craig Ringer |
---|---|
Subject | Re: Deferrable NOT NULL REFERENCES ... for two-way referential relationship (SOLVED?) |
Date | |
Msg-id | 47E9049C.2000600@postnewspapers.com.au Whole thread Raw |
In response to | Deferrable NOT NULL REFERENCES ... for two-way referential relationship (Craig Ringer <craig@postnewspapers.com.au>) |
List | pgsql-sql |
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