[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