Sql-server – Dropping duplicate|redundant Unique Constraint from FILESTREAM table

constraintsql server

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?

Best Answer

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.

Related Solutions

Sql-server – Unique Constraint with multiple null columns

I think your database is suffering from a bad data model. You might want to rethink it a little, judging from the tables you've mentioned the model isn't well normalized and you're trying to enforce a foreign key reference to a table column which isn't really a key to anything. One way to go is something like this :

CREATE TABLE EMP (ID INT PRIMARY KEY IDENTITY
                 ,EmpName VARCHAR(100) NULL)

CREATE TABLE PCTypes (id INT PRIMARY KEY IDENTITY
                     ,PcTypeName VARCHAR(100) NULL)

CREATE TABLE PC (Id INT PRIMARY KEY IDENTITY
                ,EmpId INT NULL
                ,PCName VARCHAR(100) NULL
                ,HostName VARCHAR(100) NULL
                ,PhysicalLocation VARCHAR(100) NULL
                ,PCType INT NULL)

ALTER TABLE PC ADD CONSTRAINT FK_PC_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)
ALTER TABLE PC ADD CONSTRAINT FK_PC_PCType FOREIGN KEY (PCType) REFERENCES PCTypes(id)

CREATE TABLE PCNetwork (Id INT PRIMARY KEY IDENTITY
                       ,NetworkName VARCHAR(100) NULL
                       ,EmpId INT NULL
                       ,PhysicalLocation VARCHAR(100) NULL)

ALTER TABLE PCNetwork ADD CONSTRAINT FK_PCNetwork_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)

CREATE TABLE PCNetwork_PCs (PCNetworkId INT NOT NULL
                           ,PCId INT NOT NULL
                           ,IPAddr NVARCHAR(20) NULL)

ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_Network FOREIGN KEY (PCNetworkId) REFERENCES PCNetwork(id)
ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_PC FOREIGN KEY (PCId) REFERENCES PC(id)
CREATE UNIQUE INDEX uq_pcnetworks ON PCNetwork_PCs(PCNetworkId, PCId)
CREATE UNIQUE INDEX uq_pcnetworks_ip ON PCNetwork_PCs(IPAddr)

Sql-server – add a unique constraint that ignores existing violations

The answer is "yes". You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;

This creates a unique index, only on new rows, rather than on the old rows. This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);

Best Answer

Related Solutions

Sql-server – Unique Constraint with multiple null columns

Sql-server – add a unique constraint that ignores existing violations

Related Question