Re: Composite UNIQUE across two tables?

I haven't tested these two statements, but I'm using exactly this
concept on some tables myself.
My equivalent of your users table contains some 3,000,000 records.
My equivalent of your sites table contains some 150,000 records.
And it works fine...

CREATE OR REPLACE FUNCTION "fnc_idx_sitegroupid" (p_siteid
sites.id%TYPE) RETURNS site_groups.id%TYPE AS
$body$
DECLARE
v_sitegroupid site_groups.id%TYPE ;
BEGIN
SELECT site_group_id INTO v_sitegroupid FROM sites WHERE id =
p_siteid;
RETURN v_sitegroupid;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
INVOKER;

CREATE UNIQUE INDEX "users_unq" ON "users"
USING btree ("username", (fnc_idx_sitegroupid(site_id)));

>>> Sébastien Meudec <seb(at)jack(dot)fr> 2008-03-07 9:40 >>>
Hi Bart,
I'm following this topic with interest.
Could you describe me more how you design an unique index with both a
column
name and a function name by an example
Thx.
Sebastien

________________________________________
De : pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]
De la part de Bart Degryse
Envoyé : vendredi 7 mars 2008 08:46
À : Jamie Tufnell; pgsql-sql(at)postgresql(dot)org
Objet : Re: [SQL] Composite UNIQUE across two tables?

create a function that, given the siteid returns the sitegroupid
create a unique index on username and that function

>>> "Jamie Tufnell" <diesql(at)googlemail(dot)com> 2008-03-06 20:04 >>>
Hi,

I'm remodeling our authentication tables at the moment to accomodate
future changes. I've hit a wall and thought I'd ask here for some
help. Hopefully I'm just missing something simple.

I'll do my best to explain the domain..

We have users, sites, and groups of sites.

Usernames should be unique within a site group.
Each user is tied to one, and only one, site.
Each site belongs to exactly one group, and one group can contain
many sites. (some sites are actually groupless in reality, but at
one point in my design I figured it might make it easier to force
them to be in a group of 1 -- perhaps that's part of my problem?).

A user has implied permission to access any site in their group of
sites.

So far this is what I have:

CREATE TABLE site_groups (
id serial,
name varchar(120) not null,
PRIMARY KEY (id)
);

CREATE TABLE sites (
id serial,
site_group_id integer not null,
name varchar(120) not null,
PRIMARY KEY (id),
FOREIGN KEY (site_group_id) REFERENCES site_groups (id)
);

CREATE TABLE users (
id serial,
site_id integer not null,
username varchar(120) not null,
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES sites (id)
);

The problem is the "Usernames should be unique within a site group."

I could include a site_group_id FK in the users table, and put a
UNIQUE(username, site_group_id), but it feels like I'm doing
something wrong here since the user's site_group_id is already implied
by the user's site_id.

When users login I get their username, password and site name.
Without a UNIQUE constraint on username+site_group_id, it's possible
I'll get more than one user id matching the same login information
which shouldn't happen.

I suppose I could write a trigger to enforce this uniqueness, but it
just feels like I'm probably doing something wrong elsewhere.

Any help/abuse greatly appreciated :-)

Cheers,
J.

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sq

l