Interesting Unique Index Needed - Functional?

[Dennis Gearon <gearond@fireserve.net>]

Given:

CREATE TABLE Usrs(
    usr_id SERIAL NOT NULL PRIMARY KEY,
    usr      VARCHAR(64) NOT NULL UNIQUE
);
CREATE TABLE Emails(
    email_id SERIAL NOT NULL PRIMARY KEY,
    email      VARCHAR(128) NOT NULL UNIQUE
);
CREATE TABLE EmailTypes(
    email_type_id SERIAL NOT NULL PRIMARY KEY,
    email_type      VARCHAR(64) NOT NULL UNIQUE,
    pri BOOL NOT NULL,
    multiples_allowed BOOL NOT NULL
);
CREATE TABLE UsrEmails(
    usr_id INT4 NOT NULL,
    email_id INT4 NOT NULL,
    email_type_id INT4 NOT NULL,
    validated BOOL NOT NULL DEFAULT 'T'::BOOL,
    validation _hash VARCHAR(64) NOT NULL
);
ALTER TABLE UserEmails
   ADD CONSTRAINT Usrs_11_1M_UsrEmails FOREIGN KEY (usr_id) REFERENCES 
Usrs(usr_id);
ALTER TABLE UserEmails
   ADD CONSTRAINT Emails_11_0M_UsrEmails FOREIGN KEY (email_id) 
REFERENCES Emails(email_id);
ALTER TABLE UserEmails
   ADD CONSTRAINT EmailTypes_11_0M_UsrEmails FOREIGN KEY 
(email_type_id) REFERENCES EmaiTypesl(email_type_id);

INSERT INTO Usrs( usr ) VALUES( 'John' );

INSERT INTO Emails( email ) VALUES( 'some email one' );
INSERT INTO Emails( email ) VALUES( 'some email two' );
INSERT INTO Emails( email ) VALUES( 'some email three' );
INSERT INTO Emails( email ) VALUES( 'some email four' );
INSERT INTO Emails( email ) VALUES( 'some email five' );

INSERT INTO EmailTypes( email_type, pri, multiples_allowed ) VALUES( 
'home', 'T'::BOOL, 'N'::BOOL );
INSERT INTO EmailTypes( email_type, pri, multiples_allowed) VALUES( 
'work', 'F'::BOOL, 'Y'::BOOL );
INSERT INTO EmailTypes( email_type, pri, multiples_allowed ) VALUES( 
'extra_contact', 'T'::BOOL, 'Y'::BOOL );

Now, for a little business logic:
   1/ emails are entered by client, choosing which type, and having to 
supply at least the primary type.
   2/ At first UsrEmails( validated ) = 'FALSE" and the validation_hash 
is some 160 bit number using the newer hash type.
   3/ The usual, 'send a reply to this email or if you are computer 
illiterate, click on this link' validation message gets sent out for 
each email entered. The hash is embedded in the subject or GET 
parameters as usual.
   4/ User can have MULTIPLE emails PENDING (validated = 'FALSE') for 
any of the EmailTypes, but only ONE email of any type which has 
EmailTypes( multiples_allowd ) = 'TRUE' AND UserEmails( validated ) = 'TRUE'

How can I enforce number two, i.e.
   How can I have a Unique index on UserEmails( usr_id, email_type_id ) 
where EmailTypes( multiples_allowd ) = 'TRUE' for that type AND 
UserEmails( validated ) = 'TRUE'
  


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster