-- Tables

-- Alters

update sg
set IdInvoiceItem = null
--select * 
from STOR.T_SGTINS sg
left join STOR.T_INVOICE_ITEM ii on ii.ElementId = sg.IdInvoiceItem
where ii.ElementId is null and sg.IdInvoiceItem is not null

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[STOR].[T_SGTINS]') AND name = N'UQ_T_SGTINS_IdInvoiceItem' and is_unique_constraint <> 1)
DROP INDEX [UQ_T_SGTINS_IdInvoiceItem] ON [STOR].[T_SGTINS]
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[STOR].[T_SGTINS]') AND name = N'UQ_T_SGTINS_IdInvoiceItem' and is_unique_constraint = 1)
ALTER TABLE [STOR].[T_SGTINS] DROP CONSTRAINT [UQ_T_SGTINS_IdInvoiceItem]
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[STOR].[T_SGTINS]') AND name = N'IdInvoiceItem_UQ')
ALTER TABLE [STOR].[T_SGTINS] DROP COLUMN [IdInvoiceItem_UQ]
GO

ALTER TABLE [STOR].[T_SGTINS] ADD [IdInvoiceItem_UQ] AS (CASE WHEN [IdInvoiceItem] IS NOT NULL THEN NULL ELSE [SgtinsID] END)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_SGTINS_IdInvoiceItem] ON [STOR].[T_SGTINS] ([IdInvoiceItem] ASC, [IdInvoiceItem_UQ] ASC) ON [PRIMARY]
GO

ALTER TABLE [STOR].[T_SGTINS] ADD CONSTRAINT [FK_T_INVOICE_ITEM_T_SGTINS_IdInvoiceItem]
FOREIGN KEY([IdInvoiceItem]) REFERENCES [STOR].[T_INVOICE_ITEM] ([ElementID])
GO

