I am using SQL Server 2008. I have a database table that looks like this (with non-essential columns omitted):
CREATE TABLE [dbo].[ImageDocument_FaxProperties]( [FaxPropertyID] [int] PRIMARY KEY IDENTITY(1,1), [Agent] [varchar](25) NULL, [ParentImageDocumentId] [uniqueidentifier] NULL )
I want to create a constraint in which the same Agent can have several rows as long as the values of ParentImageDocumentIds are the same for each row, but the agent cannot have rows with different ParentImageDocumentIds. I understand that this is not a good table structure, but it is outdated and I am not allowed to change it. NULL ParentImageDocumentIds should be considered different.
For instance:
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 ..}
I am wondering what is the best way to write a constraint for this. A unique index for an agent will not work, because sometimes they can have both. The unique Agent Agent, ParentImageDocumentId will allow them to have different GUIDs, which is bad. A filtered index with "WHERE ParentImageDocumentId IS NULL AND Agent IS NOT NULL" will work to prevent double NULLs, but not for different GUIDs or GUIDs and NULLs.
Below are two working solutions, however I am wondering if there is a better way. The indexed view of the schema allows me to create a more complex filtered index. An alternative was a table-level check constraint using a function, this should work, but adding a check constraint is SUPER SLOW. I assume that it re-runs my function for each row in the table, but this is not necessary to add a table-level constraint, since the function does not check for a specific row. Is there any way around this? I tend to be indexed, but I wondered if there were any alternatives (other than changing my table structure) and which alternative is the best.
Solution No. 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
Decision number 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)