I have the following tables:
CREATE TABLE Revision (
RevisionId INT PRIMARY KEY IDENTITY,
UserName NVARCHAR(256) NOT NULL,
DateTime DATETIME NOT NULL DEFAULT GETDATE()
)
CREATE TABLE MyEntity (
MyEntityId INT NOT NULL,
RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
Deleted BIT NOT NULL DEFAULT 0,
Name NVARCHAR(256) NOT NULL,
Body NVARCHAR(MAX) NOT NULL,
PRIMARY KEY (MyEntityId, RevisionId)
)
That is, the MyEntity table has all changes to an entity. When an entity is created, modified, or deleted, a new record is inserted into MyEntity and into Revision, so that the entire history is tracked.
I would like a view to have the latest version of each entity:
CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
Latest.MyEntityId,
Latest.Name,
Latest.Body
FROM dbo.MyEntity
INNER JOIN (
SELECT
MyEntityId,
RevisionId,
Name,
Body,
RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
FROM MyEntity
WHERE Deleted = 0
) AS Latest
ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1
However, I would like to create an unique index (and hence constraint) such that the Name is unique for the latest revision only. I cannot create an index on the view, because of the subquery in the view.
How can I accomplish this?
UNIQUE
constraint in the Entity table?:UNIQUE (Name)
. Doesn't that table hold the latest info and therefore the latest Name?MyEntity
table. I thought you also had another table with latest info. I don't see any other easy way, besides adding such a table. Some of the SQL-Server gurus that hang at the site will surely come up with other plans.