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, 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.
Problem Example (Simplified):
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]
This example will create the error:
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".
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.
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.
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 fiddle demo
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:
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
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.
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 '.
