Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


1
1
Sign in
to vote
Type: Bug
ID: 715467
Opened: 12/25/2011 12:55:37 PM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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
Sign in to post a workaround.
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' ;