Add ANOTHER primary key to a table which is UNIQUE

I'm having problems with adding another primary key to my table. I have 3 columns:

1. Account ID (I开发者_StackOverflow中文版dentity)
2. EmailID
3. Data field

When I made the table I had this to make the Account ID and the Email ID unique.

PRIMARY KEY (AccountID, EmailID)

I thought that would make my emailid unique, but then after I tried inserting another row with the same emailid it went through. So I thought I missed something out.

Now for my question:

1. IF, I had to use alter, how do I alter the table/PK Constraint to modify the EmailID field and make it Unique?
2. IF I decided to drop the table and made a new one, how do I make those two primary keys unique?

You may ALTER the table and add a new UNIQUE CONSTRAINT on the EmailID column.

-- This will create a constraint which enforces that the field EmailID
-- have unique values
ALTER TABLE Your_Table_Name
ADD CONSTRAINT unique_constraint_name UNIQUE (EmailID)

It's worth noting though, that altering the table to add this new unique constraint doesn't mean that you have to drop the other PRIMARY KEY constraint that you have added for the (AccountID, EmailID) pair. That is, of course, unless your business logic dictates it.

When you make the grouping of (AcountID, EmailID) the PRIMARY KEY it specifies that both the AcountID and EmailID participate in uniquely identifying each individual record in that table. So, that means that you could have the following records in the table:

 AccountID  |  EmailID                  |  Other Fields
----------------------------------------------------------
 100        |  user@company.com         |     ....
 101        |  user2@othermail.com      |     ....
 100        |  user_alternate@mail.com  |     ....

In the previous example it is possible to have two records with the same AccountID, and that is valid because the PRIMARY KEY specifies that only the (AccountID, EmailID) pair has to be unique - which it is. It makes no stipulation about AccountID being unique independently.

In conclusion, you probably want to add yet another UNIQUE constraint on AccountID. Or simply make the AccountID alone the PRIMARY KEY and then add a UNIQUE constraint on EmailID.

If both AccountID and EmailID are candidate keys then only one can be the PK the other one will need a unique constraint.

From the POV of SQL Server it doesn't matter which one you choose as the PK. Foreign Key's can reference either the PK or a unique constraint but given that the PK is the clustered index by default it probably makes sense to choose AccountID as this is presumably narrower and more stable.

It sounds like an incorrect Primary key. It's more likely that emailID is intended to be your natural key but for some reason (maybe a development standard in your organization?) you want to use a surrogate ID, AccountID but you still intend for both email ID and surrogate ID to both be unique and have a one to one relationship. If this is true then your primary key should be AccountID and you should place a unique constraint on EmailID.

If you were to recreate the table, it could look like this. I assumed EmailID was referencing an email table instead of being an email address.

CREATE TABLE dbo.AccountEmails
(
    AccountID int not null identity(1,1),
    EmailID int not null,
    Data varchar(max) null,
    constraint PK_AccountEmails PRIMARY KEY //this is a unique single column primary key
    (
        AccountID
    ),
    constraint FK_AccountEmails_EmailID FOREIGN KEY dbo.Email(EmailID) ON //this makes sure EmailID exists in the Email table
    (
        EmailID
    ),
    constraint UQ_AccountEmails_EmailID UNIQUE //unique single column unique constraint
    (
        EmailID
    ),
    constraint UQ_AccountEmails_AccountID_EmailID UNIQUE //the combination of AccountID and EmailID is also unique
    (
        AccountID,
        EmailID
    )
)

Given the fact that AccountID and EmailID are both seperately unique, I'm not sure UQ_AccountEmails_AccountID_EmailID is really necessary.