Re: almost there on a design

On Mon, 3 Mar 2003, Dennis Gearon wrote:

> I have the following, simplified three tables:
>
> CREATE TABLE Usrs(
> usr_id serial NOT NULL PRIMARY KEY,
> login varchar(32) UNIQUE,
> name text NOT NULL);
>
> CREATE TABLE EmailAddrs(
> email_addr_id serial NOT NULL PRIMARY KEY,
> email_addr varchar(320) NOT NULL UNIQUE);
>
>
> CREATE TABLE UsrEmailAddrs(
> usr_id int4 NOT NULL,
> email_addr_id int4 NOT NULL,
> pri_for_usr bool DEFAULT 0 NOT NULL,
> FOREIGN KEY (email_addr_id) REFERENCES EmailAddrs (email_addr_id),
> FOREIGN KEY (usr_id) REFERENCES Usrs (usr_id));
> CREATE UNIQUE INDEX UsrEmailAddrs_No_Dupe_UsrEmailAddrs
> ON UsrEmailAddrs (usr_id,email_addr_id);
>
> A Usr can have MANY emails in UsrEmailAddrs.
>
> If I was only allowing ONE email per user, I'd just put the email_id in
> the 'Usrs' table, and I wouldn't ask the rest of this question.
>
> The first email addr entered must have 'UsrEmailAddrs.pri_for_usr' = TRUE
> Thereafter, one and only one of the emails in 'UsrEmailAddrs' for each
> usr must have have 'UsrEmailAddrs.pri_for_usr' = TRUE. I have that handled.
>
> Because of the foreign key, no 'UsrEmailAddrs' entry can exist without
> a corresponding entry in 'Usrs'. THAT's GOOD, it's handled.
>
> Now, I want a way to prevent any entries in 'Usrs', unless there is a
> corresponding entry in 'UsrEmailAddrs' with
> 'UsrEmailAddrs.pri_for_usr' = TRUE. On the INSERT only, for now;
> I will figure out the DELETES and UPDATES later.
>
> Anyone one have any ideas?

As a first note, one (or both of those checks) have to be deferred.
Otherwise you can't add data to either table. It's easiest to defer the
UsrEmailAddrs check since it's using a foreign key, but that means you
need to insert that row (the pri_for_user=true UsrEmailAddrs row) first.

The actual check when rows are inserted into Usrs (if you're not worried
about concurrent modifications to UsrEmailAddrs) can be done I believe
with a function that does the check against UsrEmailAddrs and returns true
or false and then using that in a check constraint. You could probably
also do it via a trigger on Usrs that either allows it through or raises
an exception.