I couldn't understand exactly what you were asking for. Did you want no
duplicates between columns or no duplicates between companies or what?
This is something you will either enforce in your application code (with
your two column model) - OR - you will have to redesign your Primary and
Secondary storage to a single column (like your example). Something like
this:
CREATE TABLE contacts (
company_ID int not null,
email_address varchar(255) not null,
email_type ENUM('primary', 'secondary') default 'primary'
Primary_key (company_ID, email_address)
)
The way I defined this primary key means that for any company, an email
address can appear only once. However, that email can be used for multiple
companies (a common sales person shared between smaller businesses, for
example). Do you want it so that an email is always specific to only one
company? Options abound. Please be more specific about what rule you would
like to enforce and we can help you write a constraint that works for you.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Scott Fletcher" <[EMAIL PROTECTED]> wrote on 10/20/2004 03:54:12 PM:
> 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-
>
>
>
> Thanks,
>
> Scott
>
>
>
>
>
>
>
