Dropping duplicate|redundant Unique Constraint from FILESTREAM table

Question

I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie:

ALTER TABLE [dbo].[TableName] 
    ADD CONSTRAINT [UQ__TableName__33C4988760FC61CA] 
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

ALTER TABLE [dbo].[TableName] 
    ADD CONSTRAINT [UQ__TableName__33C49887145C0A3F] 
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

I'd like to drop one of the unique constraints, as they are duplicates. However, when I try and drop one of the two duplicate constraints, I receive the following error.

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Anyone know how to remove one of the two constraints?

Answer 1

I was able to recreate this problem.

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
    FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
 LOG ON  ( NAME = Archlog1,
         FILENAME = 'c:\data\archlog1.ldf')
GO
CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

ALTER TABLE Archive.dbo.Records WITH NOCHECK 
  ADD CONSTRAINT testfilestream UNIQUE (ID)
GO
ALTER TABLE Archive.dbo.Records
DROP CONSTRAINT testfilestream

Best I can tell this is a bug and (1) SQL Server has a hard rule relating to tables with FILESTREAM columns that says "if they try to drop ANY UNIQUE constraint on the ROWGUIDCOL column in a table with FILESTREAM, don't let them" and/or (2) There only ever is one UNIQUE constraint on a column and making another is like making an alias. So when you try and drop one, you're really dropping both.

As to how to solve it. Unfortunately, you can't add a second ROWGUIDCOL type column to a table, so that prevents a fix that's internal to the existing table. You'll probably have to create a new table with the same schema, (minus the extra unique constraint), copy the data from the current table to the new table, then drop the old table and rename the new one back to the old name.

Answer 2

Based on the situation in @TetonSig's answer this is how you can end up with just one unique constraint on the table.

Either add error handling or run the commands individually and check for errors so you don't end up dropping the table if an error occurs on the preceding steps.

CREATE TABLE dbo.RecordsNew
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)

ALTER TABLE dbo.Records  SWITCH TO dbo.RecordsNew;

DROP TABLE dbo.Records ;

EXECUTE sp_rename N'dbo.RecordsNew', N'Records', 'OBJECT' ;

BTW: This issue should be fixed in SQL Server 2012