我有一个问题.
给定两(或三)表.
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,Level 16,State 1,Line 3 Cannot create index on view
‘XXXX.dbo.TestView’ because it contains one or more UNION,INTERSECT,
or EXCEPT operators. Consider creating a separate indexed view for
each query that is an input to the UNION,or EXCEPT
operators of the original view.
备注2：
Msg 10109,Line 3 Cannot create index on view
“XXXX.dbo.TestView” because it references derived table “a”
(defined by SELECT statement in FROM clause). Consider removing the
reference to the derived table or not indexing the view.
**注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,'Brand 4',400 ),(5,'Brand 5',500 )
INSERT INTO Product
(
Id,BrandId,Code
)
VALUES
(1001,'Product 1001',1 ),(1002,'Product 1002',2 ),(1003,'Product 1003',3 ),(1004,'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,Code
)
VALUES
(6,'Brand 6',999)
INSERT INTO Product
(
Id,Code
)
VALUES
(1006,'Product 1006',999)
该产品属于不同的公司,所以我们得到
Company / Brand|Product Code 1 / 999 <-- Brand 2 / 999 <-- Product
这是独一无二的.
但如果你有2个品牌,和1个产品.
INSERT INTO Brand
(
Id,Code
)
VALUES
(7,'Brand 7',777)
(8,'Brand 8',888)
INSERT INTO Product
(
Id,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;
解决方法
http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/
基本上,您必须创建一个包含正好两行的表,并且您将在CROSS JOIN中使用该表来复制违反业务规则的行.
在您的情况下,索引视图有点难以编码,因为您表达业务规则的方式.事实上,通过索引视图检查UNIONED表上的唯一性是不允许的,如您已经看到的.
然而,约束可以以不同的方式表达：由于companyId是由品牌隐含的,您可以避免使用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,Code int
)
GO
INSERT INTO Brand
(
Id,Code
)
VALUES (1,500 )
INSERT INTO Product
(
Id,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,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.
希望这可以帮助.
