创建索引视图时如何引用表两次？如果没有它，我可以基于 2 个表和多行强制执行唯一性吗？

EDIT:添加了我试图禁止的示例数据。

这个问题类似于：无法在视图上创建聚集索引，因为我两次引用同一个表，有什么解决方法吗？ https://stackoverflow.com/questions/1011595/cannot-create-a-clustered-index-on-a-view-because-im-referencing-the-same-table但那里的答案对我没有帮助。我正在努力加强独特性，因此如果没有其他选择，“不要这样做”的答案并不能帮助我取得进步。

问题示例（简化）：

CREATE TABLE [dbo].[Object]
(
   Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   OrgId UNIQUEIDENTIFIER
)

CREATE TABLE [dbo].[Attribute]
(
   Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
   Name NVARCHAR(256) NOT NULL
)

CREATE TABLE [dbo].[ObjectAttribute]
(
   Id INT NOT NULL IDENTITY(1, 1),
   ObjectId INT NOT NULL,
   AttributeId INT NOT NULL,
   Value NVARCHAR(MAX) NOT NULL,

   CONSTRAINT FK_ObjectAttribute_Object FOREIGN KEY (ObjectId) REFERENCES [Object] (Id),
   CONSTRAINT FK_ObjectAttribute_Attribute FOREIGN KEY (AttributeId) REFERENCES Attribute (Id)
)
GO

CREATE UNIQUE INDEX IUX_ObjectAttribute ON [dbo].[ObjectAttribute] ([ObjectId], [AttributeId])
GO

CREATE VIEW vObject_Uniqueness
WITH SCHEMABINDING
AS
SELECT
    ObjectBase.OrgId
    , CAST(OwnerValue.Value AS NVARCHAR(256)) AS OwnerValue
    , CAST(NameValue.Value AS NVARCHAR(50)) AS NameValue
FROM [dbo].[Object] ObjectBase
INNER JOIN [dbo].ObjectAttribute OwnerValue
    INNER JOIN [dbo].Attribute OwnerAttribute
        ON OwnerAttribute.Id = OwnerValue.AttributeId
        AND OwnerAttribute.Name = 'Owner'
    ON OwnerValue.ObjectId = ObjectBase.Id
INNER JOIN [dbo].ObjectAttribute NameValue
    INNER JOIN [dbo].Attribute NameAttribute
        ON NameAttribute.Id = NameValue.AttributeId
        AND NameAttribute.Name = 'Name'
    ON NameValue.ObjectId = ObjectBase.Id
GO

/*
Cannot create index on view "[Database].dbo.vObject_Uniqueness". The view contains a self join on "[Database].dbo.ObjectAttribute".
*/
CREATE UNIQUE CLUSTERED INDEX IUX_vObject_Uniqueness
ON vObject_Uniqueness (OrgId, OwnerValue, NameValue)
GO

DECLARE @Org1 UNIQUEIDENTIFIER = NEWID();
DECLARE @Org2 UNIQUEIDENTIFIER = NEWID();

INSERT [dbo].[Object]
(
    OrgId
)
VALUES
    (@Org1) -- Id: 1
    , (@Org2) -- Id: 2
    , (@Org1) -- Id: 3

INSERT [dbo].[Attribute]
(
    Name
)
VALUES
    ('Owner') -- Id: 1
    , ('Name') -- Id: 2
    --, ('Others')

-- Acceptable data.
INSERT [dbo].[ObjectAttribute]
(
    AttributeId
    , ObjectId
    , Value
)
VALUES
    (1, 1, 'Jeremy Pridemore') -- Owner for object 1 (Org1).
    , (2, 1, 'Apple') -- Name for object 1 (Org1).
    , (1, 2, 'John Doe') -- Owner for object 2 (Org2).
    , (2, 2, 'Pear') -- Name for object 2 (Org2).

-- Unacceptable data.
-- Org1 already has an abject with an owner value of 'Jeremy' and a name of 'Apple'
INSERT [dbo].[ObjectAttribute]
(
    AttributeId
    , ObjectId
    , Value
)
VALUES
    (1, 3, 'Jeremy Pridemore') -- Owner for object 3 (Org1).
    , (2, 3, 'Apple') -- Name for object 3 (Org1).

-- This is the bad data. I want to disallow this.
SELECT
    OrgId, OwnerValue, NameValue
FROM vObject_Uniqueness
GROUP BY OrgId, OwnerValue, NameValue
HAVING COUNT(*) > 1

DROP VIEW vObject_Uniqueness
DROP TABLE ObjectAttribute
DROP TABLE Attribute
DROP TABLE [Object]

此示例将产生错误：

Msg 1947, Level 16, State 1, Line 2 Cannot create index on view "TestDb.dbo.vObject_Uniqueness". The view contains a self join on "TestDb.dbo.ObjectAttribute".

如图所示，我使用带有 2 个表的属性系统来表示一个对象及其值。对象的存在以及对象上的 OrgId 位于主表中，其余值是辅助表中的属性。

首先，我不明白为什么这说有一个自连接。我加入来自Object to ObjectAttribute两次。不知道我要从哪一张桌子转到同一张桌子ON clause.

其次，有没有办法让这项工作发挥作用？或者如何增强我在这里追求的独特性？我想要的最终结果是，通过Object.OrgId,我没有两个Object有的行ObjectAttribute引用它们的记录提供相同的“所有者”和“名称”值。因此，对于任何给定的 OrgId、Owner 和 Name 值都必须是唯一的Object.

我认为你可以为此创建辅助表：

CREATE TABLE [dbo].[ObjectAttributePivot]
(
   Id int primary key,
   OwnerValue  nvarchar(256),
   NameValue nvarchar(50)
)
GO

然后创建辅助触发器以保持数据同步：

create view vw_ObjectAttributePivot
as
    select
        o.Id,
        cast(ov.Value as nvarchar(256)) as OwnerValue,
        cast(nv.Value as nvarchar(50)) as NameValue
    from dbo.Object as o
        inner join dbo.ObjectAttribute as ov on ov.ObjectId = o.Id
        inner join dbo.Attribute as ova on ova.Id = ov.AttributeId and ova.Name = 'Owner'
        inner join dbo.ObjectAttribute as nv on nv.ObjectId = o.Id
        inner join dbo.Attribute as nva on nva.Id = nv.AttributeId and nva.Name = 'Name'
GO

create trigger utr_ObjectAttribute on ObjectAttribute
after update, delete, insert
as
begin
    declare @temp_objects table (Id int primary key)

    insert into @temp_objects
    select distinct ObjectId from inserted
    union
    select distinct ObjectId from deleted

    update ObjectAttributePivot set
        OwnerValue = vo.OwnerValue,
        NameValue = vo.NameValue
    from ObjectAttributePivot as o
        inner join vw_ObjectAttributePivot as vo on vo.Id = o.Id
    where
        o.Id in (select t.Id from @temp_objects as t)

    insert into ObjectAttributePivot (Id, OwnerValue, NameValue)
    select vo.Id, vo.OwnerValue, vo.NameValue
    from vw_ObjectAttributePivot as vo
    where
        vo.Id in (select t.Id from @temp_objects as t) and
        vo.Id not in (select t.Id from ObjectAttributePivot as t)

    delete ObjectAttributePivot
    from ObjectAttributePivot as o
    where
        o.Id in (select t.Id from @temp_objects as t) and
        o.Id not in (select t.Id from vw_ObjectAttributePivot as t)
end
GO

之后，您可以创建独特的视图：

create view vObject_Uniqueness
with schemabinding
as
    select
        o.OrgId,
        oap.OwnerValue,
        oap.NameValue
    from dbo.ObjectAttributePivot as oap
        inner join dbo.Object as o on o.Id = oap.Id
GO

CREATE UNIQUE CLUSTERED INDEX IUX_vObject_Uniqueness
ON vObject_Uniqueness (OrgId, OwnerValue, NameValue)
GO

sql fiddle demo http://sqlfiddle.com/#!3/59dc3/3