[thelist] Constraint/trigger help
Bill Moseley
moseley at hank.org
Fri May 18 13:08:04 CDT 2007
On Fri, May 18, 2007 at 10:57:09AM -0400, Jeffrey Barke wrote:
> Bill,
>
> Instead of storing the default mailbox in the user table, you could
> add a boolean field in the mailbox table to indicate default status.
> This would solve your integrity and circular reference concerns.
Like this?
create table mailbox (
id integer,
name text,
owner int references user(id),
default_status boolean NOT NULL DEFAULT FALSE
);
Then I need a way to ensure that only one of all the rows for an owner
has default_status true. (Seem like that comes up every so often
where you want to have a boolean where only one row can be true.)
What about using a separate table:
create table default_mailbox (
owner integer references user(id),
mailbox integer references mailbox(id),
primary key ( owner, mailbox )
);
That solves the problem of the user only having one default mailbox,
but doesn't solve the problem of making sure that they actually *own*
that default mailbox.
Must be Friday, as this shouldn't be this complex.
--
Bill Moseley
moseley at hank.org
More information about the thelist
mailing list