Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL SERVER 2008 I have a mapping table that contains a deleted date column. Since the table uses a surrogate key as its unique identifier there is a uniqueness constraint on the table. This constraint contains each of the foreign key values and the deleted date.

I have a process that runs each night and attmepts to modify this data based on an external data source. The process is attempting to update a record and set its deleted date to GETDATE(). There is currently a record in the mapping table witht he same foreign key values and a deleted date of 2011-12-17 00:17:22.157. The process is returning the following error

Violation of UNIQUE KEY constraint 'UNQ_tsitemapping_fullcolumnlist'. Cannot insert duplicate key in object 'dbo.tSiteMapping'. The duplicate key value is (4, , 5, , 1394, 154, Dec 21 2011 6:59AM).

Shouldnt the inclusion of the deleted date in the uniqueness constraint mean that this is a unique value? Why would I be getting a UNIQUE KEY constraint violation on this table? Any suggestions on how to fix this problem would be welcome.

As Per GBN's comment below. Here is the table definition.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tSiteMapping](
    [intSiteMappingID] [int] IDENTITY(1,1) NOT NULL,
    [intEntityID] [int] NOT NULL,
    [intParentCompanyID] [int] NULL,
    [intMarketSegmentID] [int] NULL,
    [intRegionID] [int] NULL,
    [intSiteID] [int] NOT NULL,
    [dtEntered] [datetime] NOT NULL,
    [dtModified] [datetime] NULL,
    [dtDeleted] [datetime] NULL,
    [intdivisionid] [int] NULL,
 CONSTRAINT [PK_lSiteMapping] PRIMARY KEY CLUSTERED 
(
    [intSiteMappingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UNQ_tsitemapping_fullcolumnlist] UNIQUE NONCLUSTERED 
(
    [intEntityID] ASC,
    [intParentCompanyID] ASC,
    [intMarketSegmentID] ASC,
    [intRegionID] ASC,
    [intSiteID] ASC,
    [intdivisionid] ASC,
    [dtDeleted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tSiteMapping]  WITH CHECK ADD  CONSTRAINT [FK_tSiteMapping_lEntity] FOREIGN KEY([intEntityID])
REFERENCES [dbo].[lEntity] ([intEntityID])
GO

ALTER TABLE [dbo].[tSiteMapping] CHECK CONSTRAINT [FK_tSiteMapping_lEntity]
GO

ALTER TABLE [dbo].[tSiteMapping]  WITH CHECK ADD  CONSTRAINT [FK_tSiteMapping_tSite] FOREIGN KEY([intSiteID])
REFERENCES [dbo].[tSite] ([intSiteID])
GO

ALTER TABLE [dbo].[tSiteMapping] CHECK CONSTRAINT [FK_tSiteMapping_tSite]
GO

ALTER TABLE [dbo].[tSiteMapping] ADD  CONSTRAINT [DF_lSiteMapping_dtEntered]  DEFAULT (getdate()) FOR [dtEntered]
GO

The following query is the query that is failing

UPDATE tSiteMapping
   SET dtDeleted = GETDATE()
 WHERE NOT EXISTS (SELECT 1
                     FROM crm...qapps a
                          JOIN crm...parent_company pc ON pc.id = a.intparentcompanyid
                          LEFT OUTER JOIN tSite s ON s.intAppID = a.appid AND s.dtDeleted IS NULL
                          LEFT OUTER JOIN lEntity e ON e.strEntity = a.enterprise_name AND e.dtDeleted IS NULL
                          LEFT OUTER JOIN lParentCompany lpc ON lpc.strParentCompany = CASE 
                                                                                           WHEN SUBSTRING(pc.companyname, 0, CHARINDEX(' - ', pc.companyname, 0)) LIKE '%Canada%' THEN
                                                                                                REPLACE(SUBSTRING(pc.companyname, 0, CHARINDEX(' - ', pc.companyname, 0)), 'Canada', 'CAN')
                                                                                           WHEN SUBSTRING(pc.companyname, 0, CHARINDEX(' - ', pc.companyname, 0)) LIKE '%  USA%' THEN
                                                                                                REPLACE(SUBSTRING(pc.companyname, 0, CHARINDEX(' - ', pc.companyname, 0)), '  USA', ' USA')
                                                                                           ELSE SUBSTRING(pc.companyname, 0, CHARINDEX(' - ', pc.companyname, 0))
                                                                                       END AND lpc.dtDeleted IS NULL
                          LEFT OUTER JOIN lDivision d ON d.strDivision = SUBSTRING(pc.companyname, CHARINDEX(' - ', pc.companyname, 0)+3, LEN(pc.companyname) - (CHARINDEX(' - ', pc.companyname, 0)+3)+1) AND d.dtDeleted IS NULL
                          LEFT OUTER JOIN lMarketSegment ms ON ms.strMarketSegment = a.strsitecategory AND ms.dtDeleted IS NULL
                          LEFT OUTER JOIN lRegion r ON r.strRegion = CASE 
                                                                         WHEN a.region = 'New England' THEN
                                                                              'North East'
                                                                         WHEN a.region = 'Northeast' THEN
                                                                              'North East'
                                                                         ELSE a.region
                                                                     END AND r.dtDeleted IS NULL
                    WHERE a.appname != ''
                      AND a.appname IS NOT NULL  
                      AND a.[server] != ''
                      AND a.[server] IS NOT NULL
                      AND a.[server] != 'CTXT'
                      AND CONVERT(VARCHAR(4000),a.appurl) != ''
                      AND a.appurl IS NOT NULL 
                      AND tSiteMapping.intSiteID = s.intSiteID
                      AND isnull(tSiteMapping.intEntityID, 0) = isnull(e.intEntityID, 0)
                      AND isnull(tSiteMapping.intParentCompanyID, 0) = isnull(lpc.intParentCompanyID, 0)
                      AND isnull(tSiteMapping.intMarketSegmentID, 0) = isnull(ms.intMarketSegmentID, 0)
                      AND isnull(tSiteMapping.intRegionID, 0) = isnull(r.intRegionID, 0)
                      AND isnull(tSiteMapping.intdivisionid, 0) = isnull(d.intdivisionid, 0)
                      AND tSiteMapping.dtDeleted IS NULL)

Here are the rows in the table that have the conflict

intSiteMappingID, intEntityID, intParentCompanyID, intMarketSegmentID, intRegionID, intSiteID, dtEntered, dtModified, dtDeleted, intdivisionid

12816, 4, NULL, 5, NULL, 1394, 2011-12-17 00:17:24.283, NULL, NULL, 154

12512, 4, NULL, 5, NULL, 1394, 2011-12-16 09:32:41.100, NULL, 2011-12-17 00:17:22.157, 154

The update should be updating the record with the NUll dtdeleted date and setting it to GETDATE() which appears to be throwing the error.

share|improve this question
1  
Please add table definition –  gbn Dec 21 '11 at 13:40
    
table definition added –  Lumpy Dec 21 '11 at 13:58
1  
And what INSERT fails? I'm not about to guess which column is which... –  gbn Dec 21 '11 at 14:12
    
Added Update statement that is failing as well as rows that are causing conflict. –  Lumpy Dec 21 '11 at 14:23
add comment

1 Answer

up vote 1 down vote accepted

I cannot really go through all this complex query but it appears that these 2 rows would be updated with same date from GETDATE() and since all other columns in the UNIQUE key are identical, this is in conflist with the UNIQUE KEY constraint.

share|improve this answer
    
ahhhhhh. I should not have missed that. Thanks yper. –  Lumpy Dec 21 '11 at 14:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.