Creating a unique column in SQL Azure Federation

Question

I'm trying to create a unique column in SQL Azure Federation, but even though the following query creates a table, I'm still able to save entries to the table with the same Column1 value. What's the correct way to set up a unique column? I need to make sure ID is the federated uniqueidentifier column whereas, for the purposes of this demo, Column1 cannot have duplicate values.

CREATE TABLE dbo.Table1(
        ID uniqueidentifier NOT NULL,
        Column1 nvarchar(50) NOT NULL,
        Column2 nvarchar(15) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
 (
     ID ASC
 ),
CONSTRAINT [PK_Table2] UNIQUE NONCLUSTERED
 (
     ID ASC,
     Column1 ASC
 )
 ) FEDERATED ON ([dist] = ID)
 GO

Don't really understand the question. Can't you create a unique constraint on Column1. — Craig, Sep 26 '12 at 04:33
This was my original question some time ago: http://stackoverflow.com/questions/12416925/create-a-table-with-a-primary-key-and-a-separate-unique-column-in-sql-azure-fede It explains what I'm trying to do. Basically, an ID column needs to be the primary key and an EMAIL_ADDRESS column needs to have a unique value. — Mark13426, Sep 26 '12 at 04:39

score 0 · Answer 1 · answered Sep 26 '12 at 05:45

0

Without testing, can't you do this

CREATE TABLE dbo.Table1(
        ID uniqueidentifier NOT NULL,
        Column1 nvarchar(50) NOT NULL,
        Column2 nvarchar(15) NULL,
CONSTRAINT [PK_Table1] UNIQUE NONCLUSTERED
 (
     ID ASC,
     Column1 ASC
 )
 ) FEDERATED ON ([dist] = ID)
 GO

CREATE UNIQUE INDEX uix_table1_column1 ON Table1(Column1)
GO

Then you will have a primary key encompassing the Federation and a separate unique constraint.

answered Sep 26 '12 at 05:45

Craig

36,306
34
114
197

That doesn't work. uix_table1_column1 won't be created because of the following error: "A unique or clustered index on a federated table must contain the federated column." I tried it with ON Table1(ID, Column1). I was able to create the table, but I could still save duplicate values for Column1. – Mark13426 Sep 26 '12 at 06:17
Please note that ID column must be a primary key whereas Column1 needs to have unique values. So, basically no duplicate values can be saved under each column. – Mark13426 Sep 26 '12 at 06:21
I would say you are out of luck then. I prefer to manage uniqueness like this in the application layer anyway. – Craig Sep 26 '12 at 06:26

Creating a unique column in SQL Azure Federation

1 Answers1