创建索引视图时如何引用表两次?我可以在没有它的情况下基于 2 个表和多行强制执行唯一性吗?

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

这个问题类似于：无法在视图上创建聚集索引，因为我两次引用同一个表，有什么解决方法吗？但是那里的答案对我没有帮助。我试图加强独特性，所以在没有替代方案的情况下回答"不要那样做"无助于我进步。

问题示例(简体(：

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两次加入ObjectAttribute。在ON子句中，我从表到同一表的任何地方都没有。

第二，有没有办法做到这一点？或者如何强制执行我要去f或这里的独特性？我想要的最终结果是，到 Object.OrgId 年，我没有两行Object行具有引用它们的ObjectAttribute记录，它们提供了相同的"所有者"和"名称"值。因此，对于任何给定Object，OrgId、所有者和名称值都需要是唯一的。

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

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

我们在这里遇到的基本问题，即强制执行您要追求的唯一性类型，是试图回答这个问题，"什么时候违规？考虑一下：

您的数据库加载了您在中引用的前两个对象您的示例(组织 1 和组织 2(
现在我们插入 ObjectAttribute(AttributeId， ObjectId， Value( VALUES (1， 3， 'Jeremy Pridemore'(

这是违规吗？根据你告诉我的，我会说"不"：我们可以继续插入 ObjectAttribute(AttributeId， ObjectId， Value( VALUES (2， 3， 'Cantalope'(，这大概没问题，对吧？因此，我们无法知道当前语句是否有效，除非我们知道下一个语句将是什么。但不能保证我们会发表第二份声明。当然，在我们下定决心第一个陈述是否可以时，没有办法知道它会是什么。

那么，我们是否应该禁止我所说的那种独立插入——插入"所有者"条目，但没有同时腐蚀"名称"条目？对我来说，这是您在这里尝试执行的操作的唯一可行方法，而强制执行此类约束的唯一方法是使用触发器。

像这样：

DROP TRIGGER TR_ObjectAttribute_Insert
GO
CREATE TRIGGER TR_ObjectAttribute_Insert ON dbo.ObjectAttribute
AFTER INSERT
AS
    DECLARE @objectsUnderConsideration TABLE (ObjectId INT PRIMARY KEY);
    INSERT INTO @objectsUnderConsideration(ObjectId)
    SELECT DISTINCT ObjectId FROM inserted; 
    DECLARE @expectedObjectAttributeEntries TABLE (ObjectId INT, AttributeId INT);
    INSERT INTO @expectedObjectAttributeEntries(ObjectId, AttributeId)
    SELECT o.ObjectId, a.Id AS AttributeId
    FROM @objectsUnderConsideration o
        CROSS JOIN Attribute a; -- cartisean join, objects * attributes
    DECLARE @totalNumberOfAttributes INT = (SELECT COUNT(1) FROM Attribute);
    -- ensure we got what we expect to get
    DECLARE @expectedCount INT, @actualCount INT;
    SET @expectedCount = (SELECT COUNT(*) FROM @expectedObjectAttributeEntries);
    SET @actualCount = (
        SELECT COUNT(*) 
        FROM @expectedObjectAttributeEntries e 
            INNER JOIN inserted i ON e.AttributeId = i.AttributeId AND e.ObjectId = i.ObjectId
    );  -- if an attribute is missing, we'll have too few; if an object is being entered twice, we'll have too many
    IF @expectedCount < @actualCount
    BEGIN 
        RAISERROR ('Invalid insertion: incomplete set of attribute values', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN 
    END
    ELSE IF @expectedCount > @actualCount
    BEGIN 
        RAISERROR ('Invalid insertion: multiple entries for same object', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN 
    END
    -- passed the check that we have all the necessary attributes; now check for duplicates
    ELSE 
    BEGIN
        -- for each object, count exact duplicate preexisting entries; reject if every attribute is a dup
        DECLARE @duplicateAttributeCount TABLE (ObjectId INT, DupCount INT);
        INSERT INTO @duplicateAttributeCount(ObjectId, DupCount)
        SELECT o.ObjectId, (
            SELECT COUNT(1)
            FROM inserted i
                INNER JOIN ObjectAttribute oa
                     ON i.AttributeId = oa.AttributeId
                    AND i.ObjectId = oa.ObjectId
                    AND i.Value = oa.Value
                    AND i.Id <> oa.Id
            WHERE oa.ObjectId = o.ObjectId
        )
        FROM @objectsUnderConsideration o
        IF EXISTS ( 
            SELECT 1
            FROM @duplicateAttributeCount d
            WHERE d.DupCount = @totalNumberOfAttributes
        )
        BEGIN
            RAISERROR ('Invalid insertion: duplicates pre-existing entry', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN 
        END
    END
GO

以上没有测试;考虑到这一点，您可能需要加入 Object 并按 OrgId 而不是 ObjectId 组织测试。您还需要类似的 UPDATE 和 DELETE 触发器。但是，希望这至少足以让您入门。

您应该考虑使用哪个 Sql Sever 版本，这对索引视图有限制。请参阅： http://msdn.microsoft.com/en-us/library/cc645993(SQL.110(.aspx#RDBMS_mgmt请参阅索引视图直接查询。创建索引视图需要执行以下步骤，这对于成功实现索引视图至关重要：

1-验证将在视图中引用的所有现有表的 SET 选项是否正确。

2-在创建任何新表和视图之前，验证会话的 SET 选项是否已正确设置。

3-验证视图定义是否具有确定性。

4-使用"与架构绑定"选项创建视图。

5-在视图上创建唯一的聚集索引。

索引视图所需的 SET 选项如果在执行查询时不同的 SET 选项处于活动状态，则计算同一表达式可能会在数据库引擎中产生不同的结果。例如，在 SET 选项CONCAT_NULL_YIELDS_NULL设置为 ON 后，表达式 'abc ' + NULL 返回值 NULL。但是，在CONCAT_NULL_YIEDS_NULL设置为 OFF 后，相同的表达式会产生"abc"。

相关内容

最新更新

热门标签：