Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Oct 12 '11 at 1:52

This question came from our site for system and network administrators.

    
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

2 Answers 2

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.

share|improve this answer
    
+1 For providing a repro. My answer shows how to fix it. –  Martin Smith Dec 25 '11 at 18:19
    
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
    
Also useful for adding and removing the IDENTITY property on columns without having to rebuild the table or add a new column. –  Martin Smith Dec 25 '11 at 18:28
    
Although that does get rid of the surplus manually created testfilestream one but actually I find that in an empty Archive database simply running the CREATE TABLE Archive.dbo.Records ... statement seems to create two UQ constraints in sys.objects so I presume what the OP is seeing may be "by design" behaviour anyway. –  Martin Smith Dec 25 '11 at 18:37
    
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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.