This question appeared couple of times on irc, so I figured I can do a blogpost about it.
First let's understand what we mean.
Assuming we have users table:
$ CREATE TABLE users ( nickname text PRIMARY KEY, password text ); CREATE TABLE
And now, let's assume we have addresses table:
$ CREATE TABLE addresses ( id serial PRIMARY KEY, user_name text NOT NULL REFERENCES users (nickname), address1 text, address2 text, city text, postal_code text, country text ); CREATE TABLE
And in here, we want to make sure that user has at least 1 address always there.
First, let's consider what would happen if we had such a mechanism in these tables now.
To insert address, we need to have user. But upon inserting to users – there are no addresses for this new user! So we need a way to delay checking.
Luckily we can do it with deferred triggers. First I'll define helper function which simply checks if there is any address for given user:
$ CREATE OR REPLACE FUNCTION check_if_user_has_addresses ( IN p_username TEXT ) RETURNS bool AS $$ SELECT EXISTS( SELECT * FROM addresses WHERE user_name = p_username ); $$ LANGUAGE SQL; CREATE FUNCTION
Then, I can create trigger on users table:
$ CREATE OR REPLACE FUNCTION user_has_addresses_trg() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN IF NOT check_if_user_has_addresses( NEW.nickname ) THEN raise exception 'User % has no addresses!', NEW.nickname; END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE FUNCTION $ CREATE CONSTRAINT TRIGGER trg_user_has_addresses AFTER INSERT OR UPDATE ON users DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE user_has_addresses_trg(); CREATE TRIGGER
Now, I will not be able to insert user:
$ INSERT INTO users (nickname) VALUES ('depesz'); ERROR: USER depesz has no addresses! CONTEXT: PL/pgSQL FUNCTION user_has_addresses_trg() line 5 at RAISE
unless, in the same transaction, I will add an address for it:
$ BEGIN; BEGIN $ INSERT INTO users (nickname) VALUES ('depesz'); INSERT 0 1 $ INSERT INTO addresses (user_name) VALUES ('depesz'); INSERT 0 1 $ COMMIT; COMMIT
This leaves one more thing – I could delete the address from addresses, and in this way end up without addresses for user:
$ DELETE FROM addresses; DELETE 1 $ SELECT * FROM users; nickname | password ----------+---------- depesz | (1 ROW)
Luckily, this is easily preventable:
$ CREATE OR REPLACE FUNCTION addresses_left_for_user() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN IF NOT check_if_user_has_addresses( OLD.user_name ) THEN raise exception 'User % has no addresses!', OLD.user_name; END IF; RETURN OLD; END; $BODY$ LANGUAGE 'plpgsql'; CREATE FUNCTION $ CREATE CONSTRAINT TRIGGER trg_addresses_left_for_user AFTER UPDATE OR DELETE ON addresses DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE addresses_left_for_user(); CREATE TRIGGER
With this in place, we can setup test:
$ BEGIN; BEGIN $ INSERT INTO users (nickname) VALUES ('other'); INSERT 0 1 $ INSERT INTO addresses (user_name) VALUES ('depesz'), ('other'); INSERT 0 2 $ SELECT * FROM users; nickname | password ----------+---------- depesz | other | (2 ROWS) $ SELECT * FROM addresses; id | user_name | address1 | address2 | city | postal_code | country ----+-----------+----------+----------+------+-------------+--------- 2 | depesz | | | | | 3 | other | | | | | (2 ROWS) $ commit; COMMIT
and now let's try to delete address:
$ DELETE FROM addresses WHERE id = 2; ERROR: USER depesz has no addresses! CONTEXT: PL/pgSQL FUNCTION addresses_left_for_user() line 5 at RAISE
What will happen, if I'll delete and insert?
$ BEGIN; BEGIN $ DELETE FROM addresses WHERE id = 2; DELETE 1 $ INSERT INTO addresses (user_name) VALUES ('depesz'); INSERT 0 1 $ commit; COMMIT
Sweet. It works. What about updates?
$ SELECT * FROM addresses; id | user_name | address1 | address2 | city | postal_code | country ----+-----------+----------+----------+------+-------------+--------- 3 | other | | | | | 4 | depesz | | | | | (2 ROWS) $ UPDATE addresses SET user_name = 'depesz' WHERE id = 3; ERROR: USER other has no addresses! CONTEXT: PL/pgSQL FUNCTION addresses_left_for_user() line 5 at RAISE
Sweet. What about case where I actually have multiple addresses and want to delete/update one of them?
$ INSERT INTO addresses (user_name) VALUES ('depesz'), ('depesz'); INSERT 0 2 $ SELECT * FROM addresses; id | user_name | address1 | address2 | city | postal_code | country ----+-----------+----------+----------+------+-------------+--------- 3 | other | | | | | 4 | depesz | | | | | 5 | depesz | | | | | 6 | depesz | | | | | (4 ROWS) $ UPDATE addresses SET user_name = 'other' WHERE id = 4; UPDATE 1 $ DELETE FROM addresses WHERE id = 5; DELETE 1 $ SELECT * FROM addresses; id | user_name | address1 | address2 | city | postal_code | country ----+-----------+----------+----------+------+-------------+--------- 3 | other | | | | | 6 | depesz | | | | | 4 | other | | | | | (3 ROWS)
Sweet. All works.
This leaves one thing though. How can I remove user? If I'll try to remove user:
$ DELETE FROM users WHERE nickname = 'other'; ERROR: UPDATE OR DELETE ON TABLE "users" violates FOREIGN KEY CONSTRAINT "addresses_user_name_fkey" ON TABLE "addresses" DETAIL: KEY (nickname)=(other) IS still referenced FROM TABLE "addresses".
Foreign key prohibits me. But if I'll try to remove addresses first, and then user, even in transaction:
$ BEGIN; BEGIN $ DELETE FROM addresses WHERE user_name = 'other'; DELETE 2 $ DELETE FROM users WHERE nickname = 'other'; DELETE 1 $ commit; ERROR: USER other has no addresses! CONTEXT: PL/pgSQL FUNCTION addresses_left_for_user() line 5 at RAISE
Luckily, this is easy to fix. In our helper function I first check if the user even exists:
$ CREATE OR REPLACE FUNCTION check_if_user_has_addresses ( IN p_username TEXT ) RETURNS bool AS $$ SELECT CASE WHEN EXISTS( SELECT * FROM users WHERE nickname = p_username ) THEN EXISTS( SELECT * FROM addresses WHERE user_name = p_username ) ELSE TRUE END; $$ LANGUAGE SQL; CREATE FUNCTION
This function will happily return true for users that don't exist:
$ SELECT check_if_user_has_addresses('bad_user'); check_if_user_has_addresses ----------------------------- t (1 ROW)
So now I can delete users:
$ BEGIN; BEGIN $ DELETE FROM addresses WHERE user_name = 'other'; DELETE 2 $ DELETE FROM users WHERE nickname = 'other'; DELETE 1 $ commit; COMMIT
Hope you'll find it useful.
Race condition! Those selects need FOR KEY SHARE.
@Vik:
good catch. New version:
I’m confused as to why you would want to design tables like this.
Allowing an address to be all nulls (i.e., no address) seems to defeat the object of forcing a user to have an address in the first place.
Wouldn’t it be better to have a user table, an addresses table and a useraddress(es) link table?
That way you can upsert the new user’s address in the addresses table (returning address id), insert the user into the users table (returning user id), then insert the two ids in the link table, all in the same transaction, no need for triggers.
That way you don’t have duplicate addresses (use e.g., country/postcode/address1), and you can have a user with more than one address (if required, or prevent this using a constraint on the link table), and allow multiple users to have the same address (if required, or prevent this using a constraint on the link table).
If you really want a user to have a no-address address, then add a dummy address in the addresses table and link to that.
Sorry if I’ve misunderstood the problem
@gary:
I designed tables in this way because content of addresses is irrelevant. I wanted to show how to make sure that there is always at least one row in sub table. What the tables contain, and what is the meaning of the data is simply not important.
I believe that to ensure that this kind of constraint is correct, you need to ensure that writes to these tables are in SERIALIZABLE isolation mode.
Ensuring that is doable with a BEFORE trigger that checks transaction_isolation and rejects anything less than SERIALIZABLE.
@David:
given the triggers as above, and default serialization mode – in what case do you think we would end up with bad data?
I’m not seeing it. What is the race condition if the SELECT statements do not have “FOR KEY SHARE”?
What do you think about enforcing it like this, without an triggers?
create table users (
nickname text primary key,
password text,
primary_address integer
);
create table addresses (
id serial primary key,
user_name text not null references users (nickname),
address1 text,
address2 text,
city text,
postal_code text,
country text
);
ALTER TABLE users ADD CONSTRAINT has_at_least_on_address FOREIGN KEY (nickname, primary_address) REFERENCES addresses (user_name, id);
@Gavin:
what about primary_address pointing to address that belongs to different user?
Sure, you can do the same thing using multi-column foreign key, but still.
I do it in the manner that GAVIN has suggested, and did it for Oracle as well.
create table users
(
nickname text primary key,
password text,
primary_address integer not null
);
create table addresses
(
id integer primary key,
user_name text not null,
address1 text,
address2 text,
city text,
postal_code text,
country text
);
ALTER TABLE addresses ADD CONSTRAINT addresses_has_a_user
FOREIGN KEY (user_name) REFERENCES users (nickname)
DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE users ADD CONSTRAINT has_at_least_on_address
FOREIGN KEY (nickname, primary_address) REFERENCES addresses (user_name, id);
First you take addresses.id from the sequence, then use addresses.id in users.primary_address. The idea is the same as with deferrable triggers, but can be used in other databases, and has better visibility than the trigger’s approach because these FK constraints are exposed on the ERD.