Уникальный индекс 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.
Надеюсь, что это поможет.
