By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

Interesting Unique Index Needed - Functional?

P: n/a
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

Nov 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.