0

How could I create a uniqueidentifier ID column and a unique nvarchar(256) email address column in SQL Azure Federation? I'm not a SQL guy, so I don't know how to set a unique constraint or if it's possible to do that in a federated database.

Edit:

I found a TSQL query to create a unique constraint, but I'm getting the following error:

"A unique or clustered index on a federated table must contain the federated column"

I think my federated column is the ID column.

Mark13426
  • 2,569
  • 6
  • 41
  • 75

1 Answers1

0

just follow what the error tells you.

federated columns are required to be part of a unique or clustered index.

see: Federation Guidelines and Limitations

so, for your unique index, the columns should be Id+emailAddress

JuneT
  • 7,840
  • 2
  • 15
  • 14
  • You mean I should combine those into one column and store data like so: GUID+emailAddress? – Mark13426 Sep 14 '12 at 01:55
  • nope, you can retain them as separate columns. what im saying is when you create the unique index for emailAddress, SQL Federation requires you to include the federation Id. so the index columns should be Id and emailAddress – JuneT Sep 14 '12 at 02:04
  • Can you post a sample CREATE TABLE query addressing what I want to do so I better understand how to create a unique column? Thanks. – Mark13426 Sep 14 '12 at 02:18
  • CREATE UNIQUE INDEX EMAIL ON Table6 (ID, Column1); did not work. :( I was able to save duplicate Column1 values to the table. – Mark13426 Sep 14 '12 at 19:07
  • If you add the federated column to the unique index, wouldn't that defeat the purpose of creating the unique index in this case? Federated queries only run on one federation member, which is why this constraint exists. The constraint the OP wants is not enforceable at the database level, because adding ID to the unique constraint would only enforce uniqueness of email within each ID column. – Giscard Biamby Oct 05 '12 at 18:11