2

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?

1 Answer 1

2

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.

2
  • 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
    Commented Sep 27, 2012 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. Commented Sep 28, 2012 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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