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?

EDIT: Added in sample data that I am trying to disallow. This question is similiar to this: Cannot create a CLUSTERED INDEX on a View because I'm referencing the same table twice,

Solution 1:

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:

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

createtrigger utr_ObjectAttribute on ObjectAttribute
after update, delete, insertasbegindeclare@temp_objects table (Id intprimary key)

    insertinto@temp_objects
    selectdistinct ObjectId from inserted
    unionselectdistinct ObjectId from deleted

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

    insertinto 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 notin (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 notin (select t.Id from vw_ObjectAttributePivot as t)
end
GO

After that, you can create unique view:

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

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

sql fiddle demo

Solution 2:

The fundamental issue that we have here, enforcing the type of uniqueness you are going for, is in trying to answer the question, "When is it a violation?" Consider this:

• Your database is loaded with the first two objects you reference in your example (Org1 and Org2)
• Now we INSERT ObjectAttribute(AttributeId, ObjectId, Value) VALUES (1, 3, 'Jeremy Pridemore')

Is this a violation? Based on what you have told me, I would say "no": we could go on to INSERT ObjectAttribute(AttributeId, ObjectId, Value) VALUES (2, 3, 'Cantalope'), and that would presumably be fine, right? So, we can't know whether the current statement is valid unless & until we know what the next statement is going to be. But there is no guarantee we will ever issue the second statement. Certainly there is no way of knowing what it will be at the time we are making up our minds whether the first statement is OK.

Should we, then, disallow free standing insertions of the type I am talking about-- where an "owner" entry is inserted, but with no simultaneous corrosponding "name" entry? To me, that is only workable approach to what you are trying to do here, and the only way to enforce that type of constraint is with a trigger.

Something like this:

DROPTRIGGER TR_ObjectAttribute_Insert
GO
CREATETRIGGER TR_ObjectAttribute_Insert ON dbo.ObjectAttribute
AFTER INSERTASDECLARE@objectsUnderConsiderationTABLE (ObjectId INTPRIMARY KEY);
    INSERTINTO@objectsUnderConsideration(ObjectId)
    SELECTDISTINCT ObjectId FROM inserted; 

    DECLARE@expectedObjectAttributeEntriesTABLE (ObjectId INT, AttributeId INT);
    INSERTINTO@expectedObjectAttributeEntries(ObjectId, AttributeId)
    SELECT o.ObjectId, a.Id AS AttributeId
    FROM@objectsUnderConsideration o
        CROSSJOIN Attribute a; -- cartisean join, objects * attributesDECLARE@totalNumberOfAttributesINT= (SELECTCOUNT(1) FROM Attribute);

    -- ensure we got what we expect to getDECLARE@expectedCountINT, @actualCountINT;
    SET@expectedCount= (SELECTCOUNT(*) FROM@expectedObjectAttributeEntries);
    SET@actualCount= (
        SELECTCOUNT(*) 
        FROM@expectedObjectAttributeEntries e 
            INNERJOIN 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<@actualCountBEGIN 
        RAISERROR ('Invalid insertion: incomplete set of attribute values', 16, 1);
        ROLLBACK TRANSACTION;
        RETURNENDELSE IF @expectedCount>@actualCountBEGIN 
        RAISERROR ('Invalid insertion: multiple entries for same object', 16, 1);
        ROLLBACK TRANSACTION;
        RETURNEND-- passed the check that we have all the necessary attributes; now check for duplicatesELSEBEGIN-- for each object, count exact duplicate preexisting entries; reject if every attribute is a dupDECLARE@duplicateAttributeCountTABLE (ObjectId INT, DupCount INT);
        INSERTINTO@duplicateAttributeCount(ObjectId, DupCount)
        SELECT o.ObjectId, (
            SELECTCOUNT(1)
            FROM inserted i
                INNERJOIN 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 ( 
            SELECT1FROM@duplicateAttributeCount d
            WHERE d.DupCount =@totalNumberOfAttributes
        )
        BEGIN
            RAISERROR ('Invalid insertion: duplicates pre-existing entry', 16, 1);
            ROLLBACK TRANSACTION;
            RETURNENDEND
GO

The above is not tested; thinking about it, you may need to join out to Object and organize your tests by OrgId instead of ObjectId. You would also need comparable triggers for UPDATE and DELETE. But, hopefully this is at least enough to get you started.

Solution 3:

You should consider which Sql Sever edition do you use, this has limitations on indexed views. see: http://msdn.microsoft.com/en-us/library/cc645993(SQL.110).aspx#RDBMS_mgmt See indexed views direct querying. The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

1-Verify the SET options are correct for all existing tables that will be referenced in the view.

2-Verify the SET options for the session are set correctly before creating any new tables and the view.

3-Verify the view definition is deterministic.

4-Create the view by using the WITH SCHEMABINDING option.

5-Create the unique clustered index on the view.

Required SET Options for Indexed Views Evaluating the same expression can produce different results in the Database Engine if different SET options are active when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc ' + NULL returns the value NULL. However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc '.