Cannot drop duplicate unique constraint on ROWGUID column on FILESTREAM table

Cannot drop duplicate unique constraint on ROWGUID column on FILESTREAM table by Martin Smith

Closed

as Fixed

Type:	Bug
ID:	715467
Opened:	12/25/2011 12:55:37 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

If a table with FILESTREAM column(s) erroneously gets redundant unique constraints added it is not possible to drop the duplicate constraint(s) via "DROP CONSTRAINT".

This raises the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column." even though the table would still meet that criteria after the drop.

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Other

English

/*Create Table*/
CREATE TABLE dbo.Records
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT UQ1 UNIQUE,
[Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

/*Whoops - Redundant constraint added*/
ALTER TABLE dbo.Records WITH NOCHECK
ADD CONSTRAINT UQ2 UNIQUE ([Id])
GO

/*Try and drop either constraint*/
ALTER TABLE dbo.Records
DROP CONSTRAINT UQ1

ALTER TABLE dbo.Records
DROP CONSTRAINT UQ2

Msg 5505, Level 16, State 2, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

The constraint is dropped

0 attachments

Posted by Microsoft on 1/20/2012 at 2:13 PM

This has been fixed in SQL 2012.

Thanks.

Posted by Microsoft on 1/20/2012 at 11:16 AM

Hi Martin,
Thanks for filing this issue about not being able to drop duplicate constraint from FILESTREAM table. This seems like a defect. We will investigate this to see how this can be mitigated in one of our releases after SQL 2012.

SQL Server

Posted by Martin Smith on 12/25/2011 at 12:56 PM

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' ;