SQL Full text Catalog on view, Error on update

Question

I created 2 views for a single table called MyBusiness, the purpose of the 2 views is to view all unapproved businesses & all approved businesses on pre-generated forms which require robust search capability. This is why I utilized a full-text catalog.

My Table Structure:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyBusiness](
    [MyBusinessID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_MyBusiness_MyBusinessID]  DEFAULT (newid()),
    [MyBusinessDateCreated] [datetime] NOT NULL CONSTRAINT [DF_MyBusiness_MyBusinessDateCreated]  DEFAULT (getdate()),
    [MyBusinessActive] [bit] NOT NULL CONSTRAINT [DF_MyBusiness_MyBusinessActive]  DEFAULT ((1)),
    [MyBusinessContactPersonName] [varchar](max) NULL,
    [MyBusinessCompanyName] [varchar](max) NULL,
    [MyBusinessContactNumber] [varchar](max) NULL,
    [MyBusinessEmail] [varchar](max) NULL,
    [MyBusinessDescription] [varchar](max) NULL,
    [MyBusinessWebPage] [varchar](max) NULL,
    [MyBusinessAddress] [varchar](max) NULL,
    [MyBusinessSpecialty] [varchar](max) NULL,
    [MyBusinessTwitter] [varchar](max) NULL,
    [MyBusinessFacebook] [varchar](max) NULL,
    [MyBusinessImageURL] [varchar](max) NULL,
    [MyBusinessApproved] [bit] NULL CONSTRAINT [DF_MyBusiness_MyBusinessApproved]  DEFAULT ((0)),
    [MyBusinessGmapLat] [varchar](max) NULL,
    [MyBusinessGmapLong] [varchar](max) NULL,
    [UserID] [nvarchar](128) NULL,
    [ServiceURL] [varchar](max) NULL,
    [EditURL] [varchar](max) NULL,
    [DeleteURL] [varchar](max) NULL,
 CONSTRAINT [PK_MyBusiness] PRIMARY KEY CLUSTERED 
(
    [MyBusinessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

My View For Approved Business:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwApprovedBusiness]
WITH SCHEMABINDING 
AS
SELECT        
    mb.MyBusinessID, 
    mb.MyBusinessDateCreated, 
    mb.MyBusinessActive, 
    mb.MyBusinessContactPersonName, 
    mb.MyBusinessCompanyName, 
    mb.MyBusinessContactNumber, 
    mb.MyBusinessEmail, 
    mb.MyBusinessDescription, 
    mb.MyBusinessWebPage, 
    mb.MyBusinessAddress, 
    mb.MyBusinessSpecialty, 
    mb.MyBusinessTwitter, 
    mb.MyBusinessFacebook, 
    mb.MyBusinessImageURL, 
    mb.MyBusinessApproved, 
    mb.MyBusinessGmapLat, 
    mb.MyBusinessGmapLong, 
    mb.UserID, 
    mbs.MyBusinessServiceType, 
    mb.ServiceURL
FROM  dbo.MyBusiness AS mb 
INNER JOIN dbo.MyBusinessService AS mbs ON mbs.MyBusinessID = mb.MyBusinessID
WHERE (mb.MyBusinessApproved = 1)

GO

I created a unique index on the MyBusinessID column so that I can add my full-text catalog:

USE [MyDB]

GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

GO


CREATE UNIQUE CLUSTERED INDEX [IDX_V1] ON [dbo].[vwApprovedBusiness]
(
    [MyBusinessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I then added my Full text Catalog:

USE [MyDB]
GO

CREATE FULLTEXT CATALOG [fiApprovedBusiness]WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT

GO

Every time the I want to update the Approved business and set it to 1 (true)

UPDATE [MyDB].[dbo].[MyBusiness] SET MyBusinessApproved = 1 where MybusinessID = '97AC226D-E58A-470A-A553-7C4A73551038'

I get the following error:

Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.vwApprovedBusiness' with unique index 'IDX_V1'. The duplicate key value is (97ac226d-e58a-470a-a553-7c4a73551038).

Answer 1

Most likely reason for this issue is FOR UPDATE trigger on the table as it have schema binding with a view .

This issue will not occur if you remove schema binding from view. or drop all triggers.

Note: schema binding views are materialized .