Thread: almost there on a design

almost there on a design

From
Dennis Gearon
Date:
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?



Re: almost there on a design

From
Stephan Szabo
Date:
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.



Re: almost there on a design

From
Dennis Gearon
Date:
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
>




Re: almost there on a design

From
Stephan Szabo
Date:
On Mon, 3 Mar 2003, Dennis Gearon wrote:

> 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.

That'll work from a function (currently), but from straight commands,
if you make it such that there must be a usremailaddrs row to make a usr,
then the second will fail.  If you try to make the usremailaddrs row
first, it fails due to the foreign key.


Re: almost there on a design

From
Dennis Gearon
Date:
Is it possible to do 'cross constraints' between two tables using a transaction and:

    SET�CONSTRAINTS�ALL�DEFERRED;

Or making both tables INITIALLY DEFERRED?

3/3/2003 12:54:17 PM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

>
>On Mon, 3 Mar 2003, Dennis Gearon wrote:
>
>> 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.
>
>That'll work from a function (currently), but from straight commands,
>if you make it such that there must be a usremailaddrs row to make a usr,
>then the second will fail.  If you try to make the usremailaddrs row
>first, it fails due to the foreign key.
>
>




Re: almost there on a design

From
Stephan Szabo
Date:
On Mon, 3 Mar 2003, Dennis Gearon wrote:

> Is it possible to do 'cross constraints' between two tables using a transaction and:
>
>     SET�CONSTRAINTS�ALL�DEFERRED;
>
> Or making both tables INITIALLY DEFERRED?

You make constraints INITIALLY DEFERRED, but yes in general, although
technically you only need to make one of the constraints deferred unless
you don't want to constraint the order that the rows are made.

PostgreSQL doesn't support deferred check constraints, and in general the
deferred trigger stuff isn't really meant for end-user use (it's really
there for supporting foreign keys), so you'll probably want to make the
foreign key from usremailaddrs->usrs be initially deferred and insert the
usremailaddrs row first.


Re: almost there on a design

From
Dennis Gearon
Date:
so make a column in EACH table,
that references the primary key of the other table,
then set one of the constraints deferrable,
write the table with that one first - inside of a transaction

Is that it?


3/3/2003 4:18:43 PM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

>
>On Mon, 3 Mar 2003, Dennis Gearon wrote:
>
>> Is it possible to do 'cross constraints' between two tables using a transaction and:
>>
>>     SET�CONSTRAINTS�ALL�DEFERRED;
>>
>> Or making both tables INITIALLY DEFERRED?
>
>You make constraints INITIALLY DEFERRED, but yes in general, although
>technically you only need to make one of the constraints deferred unless
>you don't want to constraint the order that the rows are made.
>
>PostgreSQL doesn't support deferred check constraints, and in general the
>deferred trigger stuff isn't really meant for end-user use (it's really
>there for supporting foreign keys), so you'll probably want to make the
>foreign key from usremailaddrs->usrs be initially deferred and insert the
>usremailaddrs row first.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>




Re: almost there on a design

From
Stephan Szabo
Date:
On Mon, 3 Mar 2003, Dennis Gearon wrote:

> so make a column in EACH table,
> that references the primary key of the other table,
> then set one of the constraints deferrable,
> write the table with that one first - inside of a transaction
>
> Is that it?

Well, in your case (IIRC) you can't do a straight reference
in both directions because only one of the constraints is actually
a foreign key unless you can guarantee that there will always be a row
in usremailaddrs where the primary flag is true for a given user if there
are any rows for a given user in usremailaddrs, but in general yes, make
the usremailaddrs foreign key initially deferred, do the other check in
whatever fashion and insert the usremailaddrs rows first inside a
transaction.


>
>
> 3/3/2003 4:18:43 PM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> >
> >On Mon, 3 Mar 2003, Dennis Gearon wrote:
> >
> >> Is it possible to do 'cross constraints' between two tables using a transaction and:
> >>
> >>     SET�CONSTRAINTS�ALL�DEFERRED;
> >>
> >> Or making both tables INITIALLY DEFERRED?
> >
> >You make constraints INITIALLY DEFERRED, but yes in general, although
> >technically you only need to make one of the constraints deferred unless
> >you don't want to constraint the order that the rows are made.
> >
> >PostgreSQL doesn't support deferred check constraints, and in general the
> >deferred trigger stuff isn't really meant for end-user use (it's really
> >there for supporting foreign keys), so you'll probably want to make the
> >foreign key from usremailaddrs->usrs be initially deferred and insert the
> >usremailaddrs row first.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
>