跨表的SQL Server唯一索引

Question

可以使用视图和唯一索引在表之间创建独特的索引。

但我有一个问题。

假设有两个（或三个）表。

Company
- Id
- Name

Brand
- Id
- CompanyId
- Name
- Code

Product
- Id
- BrandId
- Name
- Code

我希望确保唯一性，即以下组合的唯一性：

Company / Brand.Code

和

Company / Brand.Product/Code

是独一无二的。

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT b.CompanyId, b.Code
    FROM dbo.Brand b

    UNION ALL

    SELECT b.CompanyId, p.Code
    FROM dbo.Product p
         INNER JOIN dbo.Brand b ON p.BrandId = b.BrandId

视图创建成功。

CREATE UNIQUE CLUSTERED INDEX UIX_UniquePrefixCode
    ON TestView(CompanyId, Code)

由于使用了UNION运算符，此操作失败。

我该如何解决这种情况？

基本上，在公司内，品牌/产品的代码都不能重复。

注：

我遇到的错误是:

 

Msg 10116，级别16，状态1，第3行无法在视图“XXXX.dbo.TestView”上创建索引，因为它包含一个或多个UNION、INTERSECT或EXCEPT运算符。考虑为原始视图的每个输入查询创建单独的索引视图，该查询是UNION、INTERSECT或EXCEPT运算符的输入。

注2：

当我使用子查询时，我会收到以下错误：

 

Msg 10109，级别16，状态1，第3行无法在视图“XXXX.dbo.TestView”上创建索引，因为它引用了派生表“a”（由FROM子句中的SELECT语句定义）。考虑删除对派生表的引用或不对视图进行索引。

注3：

因此，给定品牌：

来自@spaghettidba的答案。

INSERT INTO Brand
(
    Id,
    CompanyId,
    Name,
    Code
)
VALUES 
(1, 1, 'Brand 1', 100 ),
(2, 2, 'Brand 2', 200 ),
(3, 3, 'Brand 3', 300 ),
(4, 1, 'Brand 4', 400 ),
(5, 3, 'Brand 5', 500 )

INSERT INTO Product
(
    Id,
    BrandId,
    Name,
    Code
)
VALUES
(1001, 1, 'Product 1001', 1 ),
(1002, 1, 'Product 1002', 2 ),
(1003, 3, 'Product 1003', 3 ),
(1004, 3, 'Product 1004', 301 ),
(1005, 4, 'Product 1005', 5 )

期望的是，如果我们扩展结果，品牌代码+公司或产品代码+公司应该是唯一的。

Company / Brand|Product Code
1 / 100 <-- Brand
1 / 400 <-- Brand
1 / 1   <-- Product
1 / 2   <-- Product
1 / 5   <-- Product

2 / 200 <-- Brand

3 / 300 <-- Brand
3 / 500 <-- Brand
3 / 3   <-- Product
3 / 301 <-- Brand

没有重复。如果我们有一个品牌和产品使用相同的代码。

INSERT INTO Brand
(
    Id,
    CompanyId,
    Name,
    Code
)
VALUES 
(6, 1, 'Brand 6', 999)

INSERT INTO Product
(
    Id,
    BrandId,
    Name,
    Code
)
VALUES
(1006, 2, 'Product 1006', 999)

该产品属于另一家公司所有，因此我们收到

Company / Brand|Product Code
1 / 999 <-- Brand
2 / 999 <-- Product

这是独一无二的。

但如果你有两个品牌，只有一个产品。

INSERT INTO Brand
(
    Id,
    CompanyId,
    Name,
    Code
)
VALUES 
(7, 1, 'Brand 7', 777)
(8, 1, 'Brand 8', 888)

INSERT INTO Product
(
    Id,
    BrandId,
    Name,
    Code
)
VALUES
(1007, 8, 'Product 1008', 777)

这将产生

Company / Brand|Product Code
1 / 777 <-- Brand
1 / 888 <-- Brand
1 / 777 <-- Product

这是不被允许的。

希望这样说有意义。

注释 4：

@spaghettidba的回答解决了跨表问题，第二个问题是品牌表本身存在重复。

我通过在品牌表上创建单独的索引来解决了这个问题：

CREATE UNIQUE NONCLUSTERED INDEX UIX_UniquePrefixCode23
    ON Brand(CompanyId, Code)
    WHERE Code IS NOT NULL;

- Phill

---

你是在谈论索引视图/物化视图吗？ - hrishi

在SQL Server中，Materialized View被称为Indexed View。 - Phill

Answer 1

我在2011年就曾发表过一篇类似的解决方案的博客文章。您可以在这里找到该帖子: http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/ 基本上，您需要创建一个仅包含两行记录的表，并将该表用于CROSS JOIN以复制违反业务规则的行。

在您的情况下，由于您表达业务规则的方式，编写索引视图有点困难。实际上，通过索引视图检查UNION后的表的唯一性是不允许的，正如您已经看到的那样。

然而，可以通过不同的方式表达约束：由于companyId是由brand暗示的，因此您可以避免使用UNION，只需在产品和品牌之间使用JOIN，并通过在代码本身上添加JOIN谓词来检查唯一性。

您没有提供一些示例数据，如果我为您提供，您不介意的话：

CREATE TABLE Company (
    Id int PRIMARY KEY,
    Name varchar(50)
)

CREATE TABLE Brand (
    Id int PRIMARY KEY,
    CompanyId int,
    Name varchar(50),
    Code int
)

CREATE TABLE Product (
    Id int PRIMARY KEY,
    BrandId int,
    Name varchar(50),
    Code int
)
GO

INSERT INTO Brand
(
    Id,
    CompanyId,
    Name,
    Code
)
VALUES (1, 1, 'Brand 1', 100 ),
(2, 2, 'Brand 2', 200 ),
(3, 3, 'Brand 3', 300 ),
(4, 1, 'Brand 4', 400 ),
(5, 3, 'Brand 5', 500 )



INSERT INTO Product
(
    Id,
    BrandId,
    Name,
    Code
)
VALUES
(1001, 1, 'Product 1001', 1 ),
(1002, 1, 'Product 1002', 2 ),
(1003, 3, 'Product 1003', 3 ),
(1004, 3, 'Product 1004', 301 ),
(1005, 4, 'Product 1005', 5 )

据我所知，目前还没有违反业务规则的行。

现在我们需要索引视图和两行表。

CREATE TABLE tworows (
    n int
)

INSERT INTO tworows values (1),(2)
GO

这是索引视图：

CREATE VIEW TestView
WITH SCHEMABINDING
AS
SELECT 1 AS one
FROM dbo.Brand b
INNER JOIN dbo.Product p
    ON p.BrandId = b.Id
    AND p.code = b.code
CROSS JOIN dbo.tworows AS t
GO

CREATE UNIQUE CLUSTERED INDEX IX_TestView ON dbo.TestView(one)

这次更新应该会破坏业务规则：

UPDATE product SET code = 300 WHERE code = 301

实际上，你会收到一个错误：

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.TestView' with unique index 'IX_TestView'. The duplicate key value is (1).
The statement has been terminated.

希望这能帮到你。

- spaghettidba

---

看起来我们都在2011年想到了这个点子！我现在已经在实践中使用了几次。有时候会有一些麻烦，因为SQL Server会在语句结束时而不是事务结束时检查所有约束条件，所以有时需要按正确的顺序进行更改以避免违规。如果涉及ORM，则会特别麻烦。 - Martin Smith

我认为这只能保证一个产品及其相关品牌内的唯一性。但是我可以看出，通过删除连接条件 p.BrandId = b.Id 来修复它。你同意吗？ - usr

@usr 我不确定，要求相当令人困惑。也许 Phill 可以回答这个问题。 - spaghettidba

@spaghettidba - 我更新了一些注释。我得等到明天上班才能尝试你的解决方案，但非常想试试！！！非常感谢！ - Phill

嗯，@spaghettidba，它在防止重复产品方面表现得非常好，但它允许重复的品牌代码:( 我正在尝试解决如何同时防止两者的问题。 - Phill

AH @spaghettidba - 我在我的问题中添加了另一个注释，并通过在Brand表中创建索引来修复了重复项。 - Phill