[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