March 3, 2010 at 10:50 am
My tables as follow,
CREATE TABLE [dbo].[tPickPointH](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tPickPointH] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [tPickPointH01] UNIQUE NONCLUSTERED
(
[tcoutcd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tPickPointD](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tpphidx] [smallint] NOT NULL,
[desn] [varchar](50) NOT NULL,
CONSTRAINT [PK_tPickPointD] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED
(
[tpphidx] ASC,
[desn] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tPickPointD] WITH CHECK ADD CONSTRAINT [FK_tPickPointD_tpphidx] FOREIGN KEY([tpphidx])
REFERENCES [dbo].[tPickPointH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tPickPointD] CHECK CONSTRAINT [FK_tPickPointD_tpphidx]
My insert statement as follow,
declare @idx int
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>slim river</pickpoint></pickpoints>
</data>'
BEGIN TRY
begin transaction
insert into dbo.tPickPointH(tcoutcd) values(@cout);
set @idx=scope_identity();
insert into dbo.tPickPointD
(tpphidx,desn)
select @idx,a.b.value('pickpoint[1]','varchar(50)')
from @data.nodes('/data/pickpoints') a(b);
commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg varchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
So my result as follow,
tPickPointH
idx | tcoutcd
------------------------------
1 iph
tPickPointD
idx | tpphidx | desn
------------------------------
4 7 gopeng
5 7 slim river
now, i want to perform update as follow,
declare @idx int
set @idx=1
declare @cout varchar(20)
set @cout='iph'
declare @data xml
set @data='<data>
<pickpoints><pickpoint>gopeng</pickpoint></pickpoints>
<pickpoints><pickpoint>sungai rokam</pickpoint></pickpoints>
<pickpoints><pickpoint>sungkai</pickpoint></pickpoints>
</data>'
BEGIN TRY
begin transaction
update tPickPointH set tcoutcd=@cout
where idx=@idx;
update t
set t.desn=t1.desn
from tPickPointD t
join (select @idx AS tpphidx,a.b.value('pickpoint[1]','varchar(50)') AS desn
from @data.nodes('/data/pickpoints') a(b))t1
on t.tpphidx=t1.tpphidx;
commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg varchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
There was an error. -- Violation of UNIQUE KEY constraint 'tPickPointD01'. Cannot insert duplicate key in object 'dbo.tPickPointD'.
I'm stuck
March 3, 2010 at 2:24 pm
Your constraint is
CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED
(
[tpphidx] ASC,
[desn] ASC
)
so looks like the update is probably changing
5 7 slim river
to
5 7 gopeng
but there is row id 4 already have those values.
Either you have to change your constraint or change the way you update so that no dups would be created. Is this all the code/data?
March 3, 2010 at 9:10 pm
ray-SQL (3/3/2010)
Your constraint is
CONSTRAINT [tPickPointD01] UNIQUE NONCLUSTERED
(
[tpphidx] ASC,
[desn] ASC
)
so looks like the update is probably changing
5 7 slim river
to
5 7 gopeng
but there is row id 4 already have those values.
Either you have to change your constraint or change the way you update so that no dups would be created. Is this all the code/data?
I'm still want to maintain the constraint. Can you show me, how update statement look's like?
March 3, 2010 at 9:45 pm
What ray means is that you simply need to not insert duplicated data.
--Jeff Moden
March 4, 2010 at 4:14 am
miss.delinda (3/3/2010)
I'm still want to maintain the constraint. Can you show me, how update statement look's like?
UNIQUE Constraint means unique value. how can you maintain uniqueness by inserting duplicate values ? :-P. Either Drop unique constraint or avoid duplicate values.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply