创建索引视图时如何引用表两次？我可以根据2个表和多个行强制执行唯一性吗？ [英] How do I reference a table twice when creating an indexed view? Can I enforce uniqueness based on 2 tables and multiple rows without it?

问题描述

编辑：在我试图禁止的示例数据中添加。

Added in sample data that I am trying to disallow.

此问题与此类似：无法在View上创建CLUSTERED INDEX，因为我两次引用相同的表，任何解决方法？但那里的答案对我没有帮助。我试图强制执行唯一性，所以如果没有替代方案，不要这样做的答案不能帮助我进步。

This question is similiar to this: Cannot create a CLUSTERED INDEX on a View because I'm referencing the same table twice, any workaround? but the answer there doesn't help me. I'm trying to enforce uniqueness, and so an answer of "don't do that" without an alternative doesn't help me progress.

问题示例（简化） ：

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]

此示例将创建错误：

消息1947，级别16，状态1 ，第2行
无法在视图TestDb.dbo.vObject_Uniqueness上创建索引。视图包含TestDb.dbo.ObjectAttribute上的自联接。

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

As this shows, I'm using an attribute system with 2 tables to represent one object and it's values. The existence of the object and the OrgId on an object are on the main table, and the rest of the values are attributes on the secondary table.

首先，我不明白为什么这说自我加入。我将从 Object 加入 ObjectAttribute 两次。在 ON 子句中，我无法从一个表转到同一个表。

First of all, I don't understand why this says there is a self join. I'm joining from Object to ObjectAttribute twice. No where am I going from a table to that same table in an ON clause.

第二，是否存在这方面的工作方式？或者是如何强制我要去或在这里的独特性？我想要的最终结果是，通过 Object.OrgId ，我没有两个 Object 行 ObjectAttribute 引用它们的记录，提供相同的所有者和名称值。所以OrgId，Owner和Name值对于任何给定的 Object 都必须是唯一的。

Second, is there a way to make this work? Or way to enforce the uniqueness that I'm going f or here? The end result that I want is that, by Object.OrgId, I have no two Object rows that have ObjectAttribute records referencing them providing the same 'Owner' and 'Name' values. So OrgId, Owner, and Name values need to be unique for any given Object.

推荐答案

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

I think you could create helper table for this:

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

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

And then create helper trigger to keep data synchronized:

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

之后，您可以创建唯一视图：

After that, you can create unique view:

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小提琴演示

这篇关于创建索引视图时如何引用表两次？我可以根据2个表和多个行强制执行唯一性吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！