Seldom wrong and right again, Shawn! Yes, I forgot to cover the reflexive case. I don't have a magic bullet for that scenario; you'd just have to check that with application code.
But on re-reading his original post, it occurred to me that I had misread it completely. I just sent a note outlining my new thinking to the list; maybe we can help this guy solve his problem without having to write a lot of application code ;-) Rhino ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Scott Fletcher" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 20, 2004 4:59 PM Subject: RE: How to make 1 primary key work for 2 columns???? > But rhino, your constraints don't cover the case of : > > Primary Secondary > ----------- ---------------- > [EMAIL PROTECTED] [EMAIL PROTECTED] > [EMAIL PROTECTED] [EMAIL PROTECTED] > > This passes all of your constraints (the primary key and both unique keys) > but I think it fails his business rule (I'm still not prefectly clear on > what he wanted, though). However, I believe that this is where normalizing > his data (like his example shows) goes a long way to solving his > validation problems. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > "Scott Fletcher" <[EMAIL PROTECTED]> wrote on 10/20/2004 04:37:11 PM: > > > Aw Man! I forgot about the null value... Since there will be some null > > values, so I can't use the primary key. So, I now know it is not > > possible. Thanks for reminding me that. So, seem that the workaround > > for me is to retrieve all of the emails from the primary contact and > > secondary contact into PHP variables then encrypt it then put it into > > the hidden HTML input. I'll have to create the JavaScript validation to > > encrypt the entered email address (primary and secondary) and match it > > against the hidden HTML input. If matched then prompt the user to enter > > a different email address, if not matched then it's okay to enter it > > into the database. It's the only way... > > > > Thanks, > > Scott > > > > -----Original Message----- > > From: Rhino [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, October 20, 2004 3:30 PM > > To: Scott Fletcher > > Subject: Re: How to make 1 primary key work for 2 columns???? > > > > > > ----- Original Message ----- > > From: "Scott Fletcher" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, October 20, 2004 3:54 PM > > Subject: How to make 1 primary key work for 2 columns???? > > > > > > Hi! I'm trying to figure out how to make this possible... What I have > > here is a company address, along with both the primary contact and > > secondary contact. In them is one email address from the primary > > contact and one other email address from the secondary contact. Problem > > is setting an unique primary key that treated the 2 columns as one > > column with unique email address. > > > > > > > > Let's this example below > > > > > > > > --snip- > > > > | First_Email | Second_Email | > > > > ------------------------------------------------ > > > > | [EMAIL PROTECTED] | [EMAIL PROTECTED] | > > > > ----------------------------------------------- > > > > | [EMAIL PROTECTED] | [EMAIL PROTECTED] | > > > > ----------------------------------------------- > > > > --snip- > > > > > > > > be like this below where there is no two same email address.... > > > > > > > > --snip- > > > > --------------------------------------- > > > > | [EMAIL PROTECTED] | > > > > --------------------------------------- > > > > | [EMAIL PROTECTED] | > > > > --------------------------------------- > > > > | [EMAIL PROTECTED] | > > > > --------------------------------------- > > > > | [EMAIL PROTECTED] | > > > > --------------------------------------- > > > > --snip- > > > > > > > > This will work: > > > > create table mytab > > (from_email char(100) not null, > > to_email char(100) not null, > > [other columns], > > primary key(from_email, to_email)); > > > > There's no reason the primary key can't contain several columns as long > > as > > each is defined NOT NULL. (No column of a primary key may ever contain a > > null.) > > > > The primary key, then, is the COMBINATION of values in all of the > > columns > > that make up the primary key. Therefore, the following would be allowed: > > > > from_email to_email > > [EMAIL PROTECTED] [EMAIL PROTECTED] > > [EMAIL PROTECTED] [EMAIL PROTECTED] > > > > If you need to ensure that the 'from_email' value is also unique within > > the > > table, you can add a 'unique' constraint to the definition of the > > from_email > > value. Ditto for the 'to_email' column. Therefore, the following > > definition > > ensures that the combination of 'from_email' and 'to_email' is always > > unique > > AND it ensures that no single value appears twice in 'from_email' AND it > > ensures that no single value appears twice in 'to_email'. > > > > create table mytab > > (from_email char(100) not null unique, > > to_email char(100) not null unique, > > [other columns], > > primary key(from_email, to_email)); > > > > Rhino > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
