Wendy, I know it's not good db theory, but how about having two files:
File 1 - key:UserId, holds PersonId File 2 - key:PersonId, holds UserId Then only allow access if both records are consistent. This combination can only be true if both UserId and PersonId are uniquely cojoined. Brian Leach -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin King Sent: 08 October 2004 20:49 To: [EMAIL PROTECTED] Subject: RE: [U2] Enforcing uniqueness of two fields per record? Index the user Id field and when someone enters a user ID go read the index to see if it's been used. Would that work? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wendy Smoak Sent: Friday, October 08, 2004 12:31 PM To: [EMAIL PROTECTED] Subject: [U2] Enforcing uniqueness of two fields per record? I have a situation where I _need_ two unique keys on each record. Each record has a numeric ID that matches our PERSON file, and they also need to have a userID so they can log in on a web page. It will be fairly catastrophic if I ever let two people have the same userID. It could mean showing someone's confidential financial information to another person. :/ Can UniData help at all here? The file is currently keyed by PersonID, and before I write a record I check the index to see if that userID is already there on another record. A composite key doesn't help, this is really a candidate key, and I need to enforce uniqueness on two fields. Two things I'm investigating are: a trigger to move the checking-of-the-index as close as possible to the record write & index update, having a second file keyed by UserID that will keep me from having two PersonIDs with the same UserID. Any advice? At this point I'm wishing I could right-click on the field name and check a box to enforce uniqueness! -- Wendy Smoak Application Systems Analyst, Sr. ASU IA Information Resources Management ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ ________________________________________________________________________ This email was checked by MessageLabs SkyScan before entering Microgen. ________________________________________________________________________ This email was checked on leaving Microgen for viruses, similar malicious code and inappropriate content by MessageLabs SkyScan. DISCLAIMER This email and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information. In the event of any technical difficulty with this email, please contact the sender or [EMAIL PROTECTED] Microgen Information Management Solutions http://www.microgen.co.uk ------- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/