Re: almost there on a design - Mailing list pgsql-general

3/3/2003 10:25:12 AM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

If I create the necessary fields first in this order, no deferrment seems necessary:

I'm thinking about trying to create a user which only has access to selects and stored
procedures, so the web interface can ONLY use functions which take place in a transaction and
take care of it that way.


create an email,
create a usr
create a usr email.
>
>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.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>