September 2, 2008 at 9:06 pm
I have table which has data but data has been mess up. column supposed to be unque and my task is to fix the bad data.
i check the data from query like this:
select count(*),projectmemberuserid,projectid,financecycleid
from tblprojectmembers
where projectmemberuserid is not null
group by projectmemberuserid, projectid, financecycleid
having count(*) >1
Result:
2517641453609
2505918123609
2498492182609
2512281506609
2515314841580
2511852861617
31894781506609
2512381756607
2587034841607
projectmemberid- pk, projectid = fk not null, projectmemberuserid fk, null.
projectmemberuserid is equivalent to userid in another tbluser which has all the users. i know there is some proc doing wrong while inserting data. right now i have to remove the bad data from tblprojectmembers
Any suggestion?
sagar
September 2, 2008 at 9:39 pm
Sagar (9/2/2008)
projectmemberid- pk, projectid = fk not null, projectmemberuserid fk, null.projectmemberuserid is equivalent to userid in another tbluser which has all the users. i know there is some proc doing wrong while inserting data. right now i have to remove the bad data from tblprojectmembers
Any suggestion?
sagar
"projectmemberid- pk" - what does it mean?
If it would be PK it could not be dupliicated.
Do you have any PK in this table?
Any unique column?
If not - can you add IDENTITY column?
_____________
Code for TallyGenerator
September 2, 2008 at 9:48 pm
table structure is like this:
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblProjectMembers](
[ProjectMemberId] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[ProjectMemberUserID] [int] NULL,
[JobTypeID] [int] NULL,
[Description] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblProjectMembers_Description] DEFAULT (''),
[ManagerOfJobtypeID] [int] NULL,
[SiteID] [int] NULL,
[CostCenterID] [int] NULL,
[CompanyCodeID] [int] NULL,
[SubJobTypeID] [int] NULL,
[FinanceCycleId] [int] NULL,
CONSTRAINT [PK_tblProjectMembers] PRIMARY KEY CLUSTERED
(
[ProjectMemberId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [resource]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblCostCenter_CostCenterId] FOREIGN KEY([CostCenterID])
REFERENCES [dbo].[tblCostCenters] ([CostCenterId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblCostCenter_CostCenterId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblFinanceCycles_FinanceCycleId] FOREIGN KEY([FinanceCycleId])
REFERENCES [dbo].[tblFinanceCycles] ([FinanceCycleId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblFinanceCycles_FinanceCycleId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblJobType_JobTypeId] FOREIGN KEY([JobTypeID])
REFERENCES [dbo].[tblJobTypes] ([JobTypeId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblJobType_JobTypeId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblProjects_ProjectId] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[tblProjects] ([ProjectId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblProjects_ProjectId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblSites_SiteId] FOREIGN KEY([SiteID])
REFERENCES [dbo].[tblSites] ([SiteId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblSites_SiteId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblSubJobTypes_SubJobTypeId] FOREIGN KEY([SubJobTypeID])
REFERENCES [dbo].[tblSubJobTypes] ([SubJobTypeId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblSubJobTypes_SubJobTypeId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMembers_tblUsers_ProjectMemberUserId] FOREIGN KEY([ProjectMemberUserID])
REFERENCES [dbo].[tblUsers] ([UserId])
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [FK_tblProjectMembers_tblUsers_ProjectMemberUserId]
GO
ALTER TABLE [dbo].[tblProjectMembers] WITH NOCHECK ADD CONSTRAINT [CK_tblProjectMembers_JobTypeId_ProjectMemberUserId] CHECK (([JobTypeId] IS NOT NULL OR [ProjectMemberUserId] IS NOT NULL))
GO
ALTER TABLE [dbo].[tblProjectMembers] CHECK CONSTRAINT [CK_tblProjectMembers_JobTypeId_ProjectMemberUserId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ensure that either JobTypeId or ProjectMemberUserId is inserted.' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tblProjectMembers', @level2type=N'CONSTRAINT', @level2name=N'CK_tblProjectMembers_JobTypeId_ProjectMemberUserId'
there is already a identity column.
could not figuer out why it could happen.
sagar.
September 2, 2008 at 11:07 pm
There is the query to identify rows to be deleted.
Once you see the candidates for deletion are correct - run commented DELETE.
SELECT T.*
--DELETE T
FROM tblprojectmembers T
INNER JOIN (
select count(*), MIN(ProjectMemberId) MemberID_ToStay,
projectmemberuserid, projectid, financecycleid
from tblprojectmembers
where projectmemberuserid is not null
group by projectmemberuserid, projectid, financecycleid
having count(*) >1
)DT ON T.projectmemberuserid = DT.projectmemberuserid
AND T.projectid = DT.projectid
AND T.financecycleid = DT.financecycleid
AND T.ProjectMemberId > DT.MemberID_ToStay
Once you've done it immediately enforce unique constraint:
ALTER TABLE [dbo].[tblProjectMembers] ADD CONSTRAINT
[U_tblProjectMembers] UNIQUE (projectmemberuserid, projectid, financecycleid)
After that any attempt to insert duplicates will fail and throw an error.
_____________
Code for TallyGenerator
September 2, 2008 at 11:16 pm
thank you very much. i was also thinking about adding unique constraint. can i do while database is online line in production or we need to be offline.
thank
sagar
September 3, 2008 at 12:00 am
You can add constraint at any time.
Just be aware - you live application will throw error when trying insert duplicates.
_____________
Code for TallyGenerator
September 3, 2008 at 10:29 pm
when i try to delete it gets error
like this
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_tblProjectMemberDetails_tblProjectMembers_ProjectMemberId". The conflict occurred in database "resource", table "dbo.tblProjectMemberDetails", column 'ProjectMemberId'.
The statement has been terminated.
********************************
tblprojectmemberdetails structure is like this
CREATE TABLE [dbo].[tblProjectMemberDetails](
[ProjectMemberId] [int] NOT NULL,
[ProjectForecastYear] [int] NOT NULL,
[ProjectForecastQuarter] [int] NOT NULL,
[ProjectForecastValue] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_tblProjectForecasts] PRIMARY KEY CLUSTERED
(
[ProjectMemberId] ASC,
[ProjectForecastYear] ASC,
[ProjectForecastQuarter] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [resource]
GO
ALTER TABLE [dbo].[tblProjectMemberDetails] WITH NOCHECK ADD CONSTRAINT [FK_tblProjectMemberDetails_tblProjectMembers_ProjectMemberId] FOREIGN KEY([ProjectMemberId])
REFERENCES [dbo].[tblProjectMembers] ([ProjectMemberId])
GO
ALTER TABLE [dbo].[tblProjectMemberDetails] CHECK CONSTRAINT [FK_tblProjectMemberDetails_tblProjectMembers_ProjectMemberId]
do i have to disable constraint ?
September 3, 2008 at 11:44 pm
i have tried this which delete the row that matches with duplicate from tblprojectmember did like this
delete t1
from tblprojectmemberdetails t1
inner join (
select projectmemberID
from tblfiltereddata)t2
on t1.projectmemberID=t2.projectmemberID
then i was able to delete but i was not sure if it is correct way to do it.
Then when i try to create unique constraint
ALTER TABLE [dbo].[tblProjectMembers] ADD CONSTRAINT
[U_tblProjectMembers] UNIQUE (projectmemberuserid, projectid, financecycleid)
gave me error
Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.tblProjectMembers' and index name 'U_tblProjectMembers'. The duplicate key value is ( , 33, 583).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
can some body help give me a good direction...
thanks
sagar
September 5, 2008 at 6:49 am
Just a thought, but while the application(s) are down, could the foreign key reference constraints be dropped, then the delete done, then the foreign key reference constraints be put back? I'm asking because I'd love to know if that's a viable way to solve this kind of problem. Anyone?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply