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