Thread: almost there on a design
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?
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.
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 >
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.
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.
>
>
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.
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 >
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 > > > > >