Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create two columns in a federated table where ID is a primary key and EMAIL_ADDRESS can never contain duplicate values. Think of it like a user being able to register only one email address at a time, and each user has a unique ID.

CREATE TABLE dbo.PERSON
(
    ID uniqueidentifier PRIMARY KEY NOT NULL
    , EMAIL_ADDRESS nvarchar(256) NOT NULL
    CONSTRAINT [PK_C1] UNIQUE CLUSTERED
    (
    ID ASC,
    EMAIL_ADDRESS ASC
    )
) FEDERATED ON ([dist] = ID);
GO

CREATE UNIQUE INDEX users_email_address 
    ON PERSON(ID, EMAIL_ADDRESS);
GO

This will allow me to create a table, but I can still save duplicate values under EMAIL_ADDRESS.

How can I prevent duplicate email address entries?

share|improve this question

1 Answer 1

You can only have uniqueness validation within a single federation, same as partitioned table allow for aligned uniqueness validation only within a partition (federations don't have an 'unaligned' equivalent). The solution is trivial: create a separate, non-federated, Azure DB used only to validate email uniqueness.

share|improve this answer
    
Doesn't that defeat the purpose of using federated databases though? Using a non-federated table should not allow me to scale out that specific table. –  Mark13426 Sep 27 '12 at 22:30
1  
Sure you can scale it. If you have that many subscribers as to fill 150Gb of email addresses, simply create a different federation partitioned by email. Or by email hash. That will allow you to scale as much as you like and have uniqueness enforced. –  Remus Rusanu Sep 28 '12 at 7:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.