Уникальный индекс 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 не может дублироваться внутри компании.

Примечания:

Ошибка, которую я получаю:

Msg 10116, уровень 16, состояние 1, строка 3 Невозможно создать индекс в представлении 'XXXX.dbo.TestView', потому что он содержит один или несколько UNION, INTERSECT, или EXCEPT. Рассмотрите возможность создания отдельного индексированного представления для каждый запрос, который является входом в UNION, INTERSECT или EXCEPT операторы исходного вида.

Примечания 2:

Когда я использую вспомогательный запрос, я получаю следующую ошибку:

Msg 10109, уровень 16, состояние 1, строка 3 Невозможно создать индекс в представлении "XXXX.dbo.TestView", потому что он ссылается на производную таблицу "a", (определяется выражением SELECT в предложении FROM). Рассмотрите возможность удаления ссылаться на производную таблицу или не индексировать представление.

** Примечания 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.

Мне удалось решить эту проблему, создав отдельный индекс в таблице бренда:

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

Ответ 1

Я написал о подобном решении еще в 2011 году. Вы можете найти сообщение здесь: http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/

В принципе, вам нужно создать таблицу, содержащую ровно две строки, и вы будете использовать эту таблицу в CROSS JOIN для дублирования строк, которые нарушают ваши бизнес-правила.

В вашем случае индексированное представление немного сложнее кодировать из-за того, как вы указали бизнес-правило. Фактически, проверка уникальности в UNIONed таблицах с помощью индексированного представления не разрешена, как вы уже видели.

Однако ограничение может быть выражено по-другому: поскольку компания подразумевает бренд, вы можете избежать 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.

Надеюсь, что это поможет.