almost there on a design
От | Dennis Gearon |
---|---|
Тема | almost there on a design |
Дата | |
Msg-id | D0GE95GCJG6UR4Y98RO2ZMJVUTXT86.3e638c13@cal-lab обсуждение исходный текст |
Ответы |
Re: almost there on a design
|
Список | 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?
В списке pgsql-general по дате отправления: