How to create a unique column in SQL Azure Federation

Question

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?

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.