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'
