跨表的SQL Server唯一索引

基本上可以使用视图和唯一索引在整个表之间创建唯一索引。

我有一个问题。

给定两个（或三个）表。

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

我该如何解决这种情况？

基本上，两者的代码都Brand/Product不能在公司内重复。

笔记：

我得到的错误是：

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

注2：

当我使用子查询时，出现以下错误：

消息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 )

如果我们扩展结果，则期望是Brand Code + Company或Product Code + Company唯一。

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

这是独一无二的。

但是，如果您有2个品牌和1个产品。

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的答案解决了跨表问题，第二个问题是Brand表本身中的重复项。

我设法通过在brand表上创建单独的索引来解决此问题：

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