Violation of PRIMARY KEY constraint, when using hierarchical data

Question

question

MaverickUK asked Jul 12, '10

Violation of PRIMARY KEY constraint, when using hierarchical data

The search query in an application I'm working on the user has the ability to select one or more locations from a treeview that represents the possible locations.

The query calls a stored procedure in the database that performs the search. Part of this procedure automaticallys adds all the child locations of a selected location - this is what's causing my problem.

Problem: If the user selects a top level location, then drills down into this location and selects a child of this location it causes the stored procedure to re-add this sub location, which is what causes the primary key violation.

Error message: Violation of PRIMARY KEY constraint 'PK_#04459E07_0539C240'. Cannot insert duplicate key in object 'dbo.@GeographiesRecursive'.

Code
This is the code which automatically adds all the possible sub-locations to the selected location - but also triggers the error when a manually supplied GeographyId is re-added automatically from the child search.

IF @IncludeSubLocations = 1 BEGIN                    
                    
    WITH GeographyHierarchy (GeographyId, ParentGeographyId ) AS                    
    (                    
       -- Base case                    
       SELECT                    
          G.GeographyId,                    
          G.ParentGeographyId                    
       FROM Geography AS G                    
       WHERE EXISTS ( SELECT 'Exists' FROM @Geographies AS CSV WHERE CSV.GeographyId = G.GeographyId )                    
                    
       UNION ALL                    
                    
       -- Recursive step                    
       SELECT                    
          G.GeographyId,                    
          G.ParentGeographyId                    
       FROM Geography AS G                    
          INNER JOIN GeographyHierarchy AS GH ON                    
             G.ParentGeographyId = GH.GeographyId                    
    )                    
                    
    -- Add recursively found locations                    
    INSERT INTO @GeographiesRecursive                    
    ( GeographyId )                    
    SELECT                    
    GeographyId                     
    FROM                    
    GeographyHierarchy AS G                    
END

Database structures

DECLARE @GeographiesRecursive TABLE ( GeographyId INT PRIMARY KEY CLUSTERED ) -- Used if 'include sub-locations' is enabled

CREATE TABLE [dbo].[Geography](                    
    [GeographyId] [int] IDENTITY(1,1) NOT NULL,                    
    [Name] [varchar](100) NOT NULL,                    
    [Level] [tinyint] NOT NULL,                    
    [ParentGeographyId] [int] NOT NULL,                    
    [Code] [varchar](50) NULL                    
)

stored-procedures insert

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-07-12T14:17:50Z

TG answered Jul 12, '10

I don't know your ddl structure or your business rules but this may work:

INSERT INTO @GeographiesRecursive            
    ( GeographyId )            
SELECT GeographyId             
FROM   GeographyHierarchy AS G            
left outer join @GeographiesRecursive gr             
       on gr.GeographyId = g.GeographyId            
where  gr.GeographyId is null

If you still get a PK violation then you may need a GROUP BY g.geographyid after the WHERE clause.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question