Re: Composite UNIQUE across two tables?

Hello Jamie,

I vote against duplicating site_group_id in the users table and the
proposed unique constraint with a function. Because all those might
fail, if you ever want to change the relationship between a site and a
site group.

My advise would be to have two triggers for insert/update on the site
and users table that check the uniqueness of the username with the
site_group. A have made some tests with inserts and updates on the
existing users and sites and these two functions seem to work.

One remark about your schema: If you use PostgreSQL, use the "text"
datatype for strings, since you don't limit yourself with the string
length. For my tests, I have modified your posted schema a bit, to
unify all column names to "name". You should set up an extensive
testcase if you haven't done already which should check every
combination of insert, update and delete on the three tables and see
if those are supposed to work, or should be restricted because of your
uniqueness constraints.

CREATE TABLE site_groups (  id serial primary key,  name text not null
);

CREATE TABLE sites (  id serial primary key,  site_group_id integer not null references site_groups,  name text not
null
);

CREATE TABLE users (  id serial,  site_id integer not null references sites,  name text not null
);

create or replace function user_check_unique_site_group()
returns trigger
as $$
declare c int; sg int;
begin -- get site_group id from site select into sg site_group_id from sites where id = NEW.site_id;
 -- check if we find any equal user names in the site group select into c count(*) from users, sites where
users.site_id= sites.id   and sites.site_group_id = sg   and users.name = NEW.name;
 
 -- nothing found, this user name is ok if c = 0 then   return NEW; end if;
 raise exception 'username is not unique with site group'; return NULL;
end;
$$ language plpgsql;

create trigger user_check_unique_site_group
before update or insert
on users
for each row
execute procedure user_check_unique_site_group();

create or replace function sites_check_unique_username()
returns trigger
as $$
declare c int;
begin -- if the site group is unmodified we're safe if NEW.site_group_id = OLD.site_group_id then   return NEW; end
if;
 -- check if the same username is in the old and new site group select into c count(*) from users, sites where
users.site_id= sites.id   and sites.site_group_id = NEW.site_group_id   and users.name in (                      select
users.name                     from users, sites                      where users.site_id = sites.id
   and sites.site_group_id = OLD.site_group_id                     );
 
 -- nothing found, we're safe if c = 0 then   return NEW; end if;
 raise exception 'username is not unique with site group'; return NULL;
end;
$$ language plpgsql;

create trigger sites_check_unique_username
before update
on sites
for each row
execute procedure sites_check_unique_username();

-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org