Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 657982E016E for ; Fri, 7 Mar 2008 04:54:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84179-04 for ; Fri, 7 Mar 2008 04:54:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (unknown [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id CBDE72E0066 for ; Fri, 7 Mar 2008 04:54:21 -0400 (AST) Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise; Fri, 07 Mar 2008 09:54:21 +0100 Message-Id: <47D110C0.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Fri, 07 Mar 2008 09:54:09 +0100 From: "Bart Degryse" To: "'Jamie Tufnell'" , "=?UTF-8?Q?S=C3=A9bastien=20Meudec?=" , Subject: Re: Composite UNIQUE across two tables? References: <47D100E0.A3DD.0030.0@indicator.be><20080307083421.26A9D700009D@mwinf2f21.orange.fr> In-Reply-To: <20080307083421.26A9D700009D@mwinf2f21.orange.fr> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part5D7BC6A1.2__=" X-imss-version: 2.050 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/38 X-Sequence-Number: 30366 --=__Part5D7BC6A1.2__= Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] De la part de Bart Degryse Envoyé : vendredi 7 mars 2008 08:46 À : Jamie Tufnell; pgsql-sql@postgresql.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" 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@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sq l --=__Part5D7BC6A1.2__= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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%TY= PE) 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 =3D 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=C3=A9bastien Meudec <seb@jack.fr> 2008-03-07 9:40 >>>
H= i 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@postgresql.org [mailto:pgsql-= sql-owner@postgresql.org]
De la part de Bart Degryse
Envoy=C3=A9 : = vendredi 7 mars 2008 08:46
=C3=80 : Jamie Tufnell; pgsql-sql@postgresql.= 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@googlemail.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.
Ea= ch 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,
&nbs= p;   PRIMARY KEY (id)
);

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

CREATE TABLE users (
 &nbs= p;  id serial,
    site_id integer not null,
&nbs= p;   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@postgresql.org)
To make changes = to your subscription:
http://mail.postgresql.org/mj/m= j_wwwusr?domain=3Dpostgresql.org&extra=3Dpgsql-sq
l


<= /DIV> --=__Part5D7BC6A1.2__=--