Re: Composite UNIQUE across two tables? - Mailing list pgsql-sql

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" <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.
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