Re: Interesting Unique Index Needed - Functional?

PS, I forgot, the 'Index' UserEmailshas to take into account the
EmailTypes('multiples_allowed') field.

Dennis Gearon wrote:

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