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]
