我是一名IT技术翻译,以下是您需要翻译的内容:
我正在使用SQL Server 2008。我有一个数据库表格,长这样(不重要的列被省略了):
CREATE TABLE [dbo].[ImageDocument_FaxProperties](
[FaxPropertyID] [int] PRIMARY KEY IDENTITY(1,1),
[Agent] [varchar](25) NULL,
[ParentImageDocumentId] [uniqueidentifier] NULL
)
我希望创建一个约束条件,使得同一代理商可以有多行数据,只要每行的ParentImageDocumentIds相同,但是代理商不能有具有不同ParentImageDocumentIds的行。我知道这不是一个好的表结构,但这是遗留问题,我不被允许更改它。NULL ParentImageDocumentIds应该被视为不同的值。
例如:
PK Agent ParentImageDocumentId -This is ok
# person1 {D09C3900-0300} {.. other columns ..}
# person1 {D09C3900-0300} {.. other columns ..}
PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 NULL {.. other columns ..}
# person1 NULL {.. other columns ..}
PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 NULL {.. other columns ..}
# person1 {A13E5B21-93DE} {.. other columns ..}
PK Agent ParentImageDocumentId -Check constraint prevents 2nd row insertion
# person1 {D09C3900-0300} {.. other columns ..}
# person1 {A13E5B21-93DE} {.. other columns ..}
我想知道为此编写约束的最佳方法。对代理商进行唯一索引不起作用,因为他们有时候可以同时拥有两个以上的代理商。在代理商、ParentImageDocumentId上创建唯一性索引会允许它们具有不同的GUID,这是不允许的。使用“WHERE ParentImageDocumentId IS NULL AND Agent IS NOT NULL” 过滤的索引可以避免双重空值,但不能避免不同的GUID或GUID和空值。
以下是两种可行的解决方案,但我想知道是否有更好的方法。已索引的绑定图允许我创建一个更复杂的过滤索引。另一种替代方法是使用一个函数的表级检查约束,这应该可以工作,但添加检查约束非常慢。我猜测它会为表中的每一行重新运行我的函数,但对于添加表级约束来说,这是不必要的,因为该函数并未检查特定行。有没有方法可以避免这种情况?我倾向于使用索引视图,但想知道除了更改表结构之外还有哪些替代方案,并且哪种备选方案是最佳的。
解决方案#1:
CREATE VIEW ImageDocument_FaxProperties_Assignments WITH SCHEMABINDING
AS
SELECT Agent, ParentImageDocumentId, COUNT_BIG(*) as numPages FROM dbo.ImageDocument_FaxProperties
WHERE Status IN ( 'PROC', 'LINKING' )
AND Agent IS NOT NULL
GROUP BY Agent, ParentImageDocumentId
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_ImageDocument_FaxProperties_Assignments_Unique] ON [ImageDocument_FaxProperties_Assignments] (Agent)
GO
解决方案 #2:
CREATE FUNCTION ImageDocument_FaxProperties_Assignments_CheckConstraint() RETURNS BIT
AS
BEGIN
DECLARE @Result BIT = 0;
WITH Assignments AS
(SELECT Agent, DENSE_RANK() OVER (PARTITION BY Agent ORDER BY ParentImageDocumentId) AS assignmentNum
FROM dbo.ImageDocument_FaxProperties
WHERE Status IN ( 'PROC', 'LINKING' )
AND Agent IS NOT NULL
)
SELECT @Result = 1 FROM Assignments WHERE assignmentNum > 1
RETURN @Result
END
GO
ALTER TABLE [ImageDocument_FaxProperties]
ADD CONSTRAINT ImageDocument_FaxProperties_Assignments_Unique CHECK (dbo.ImageDocument_FaxProperties_Assignments_CheckConstraint() = 0)