From: | Dennis Gearon <gearond(at)cvc(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | almost there on a design |
Date: | 2003-03-03 17:08:35 |
Message-ID: | D0GE95GCJG6UR4Y98RO2ZMJVUTXT86.3e638c13@cal-lab |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | scott.marlowe | 2003-03-03 17:09:44 | Re: Hosting a data file on a SAN |
Previous Message | Gabriele Bartolini | 2003-03-03 16:27:42 | Hwo to set timeout for PHP persistent connections |