6

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?

  • How do you know those are the precise constraint definitions? Did you script them out using SSMS? – Nick Chammas Nov 27 '11 at 6:58
  • Have you tried dropping both of them individually? – gbn Nov 27 '11 at 9:05
3

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.

  • Nice! I work with Partitioned Tables all the time and somehow I'd never realized that SWITCH could be used like you indicated. Exactly the kind of thing I hoped would come out of participating here. – TetonSig Dec 25 '11 at 18:26
  • I thought so too, but if you'll look again the SerialNumber has a UNIQUE constraint too. That's why there are two initially. It came with the sample filestream code, but it's not necessary and confuses the issue so I'll remove it. – TetonSig Dec 25 '11 at 18:40
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy