Unique Key Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique Key Problem

Author

Topic

Cralis
Starting Member

11 Posts

Posted - 2008-08-15 : 06:19:43

Hi guys,

I have a table called UserAircraftComponent. I want to ensure the same Aircraftid, usedid and componentid can't be duplicated. I have a [deleted] field which is a DateTime. If this is NULL, the record is active. If it's deleted, I put GetDate() into that column. This enables the user to have any number of deleted rows, but only one active.

Problem is, my SP is returning an error:

{"Violation of UNIQUE KEY constraint 'uq_useraircraftcomponent'. Cannot insert duplicate key in object 'dbo.UserAircraftComponent'.\r\nThe statement has been terminated."}

The SP I am running is:

UPDATE dbo.[UserAircraftComponent]
SET [Deleted] = GETDATE()
WHERE useraircraftid = @useraircraftid
AND componentid = @componentid

Below is the (rather messy!) table schema.... Can anyone tell me why on earth I am getting a key violation?? Surely GetDate is unique... unless I delete the same item in the same second... which I'm not doing...

CREATE TABLE [dbo].[UserAircraftComponent](
[id] [int] IDENTITY(1,1) NOT NULL,
[useraircraftid] [int] NOT NULL,
[deleted] [datetime] NULL,
[componentid] [int] NOT NULL,
[usedid] [int] NOT NULL DEFAULT ((0)),
CONSTRAINT [pk_useraircraftcomponent] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uq_useraircraftcomponent] UNIQUE NONCLUSTERED
(
[useraircraftid] ASC,
[componentid] ASC,
[usedid] ASC,
[deleted] 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].[UserAircraftComponent] WITH CHECK ADD CONSTRAINT [fk_useraircraft_component] FOREIGN KEY([componentid])
REFERENCES [dbo].[Component] ([id])
GO
ALTER TABLE [dbo].[UserAircraftComponent] CHECK CONSTRAINT [fk_useraircraft_component]
GO
ALTER TABLE [dbo].[UserAircraftComponent] WITH CHECK ADD CONSTRAINT [fk_useraircraftcomponent_used] FOREIGN KEY([usedid])
REFERENCES [dbo].[ComponentTypeUseDescription] ([id])
GO
ALTER TABLE [dbo].[UserAircraftComponent] CHECK CONSTRAINT [fk_useraircraftcomponent_used]
GO
ALTER TABLE [dbo].[UserAircraftComponent] WITH CHECK ADD CONSTRAINT [fk_useraircraftcomponent_useraircraft] FOREIGN KEY([useraircraftid])
REFERENCES [dbo].[UserAircraft] ([id])
GO
ALTER TABLE [dbo].[UserAircraftComponent] CHECK CONSTRAINT [fk_useraircraftcomponent_useraircraft]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-15 : 06:23:21

check in u r table if same user exists for given useraircraftid = @useraircraftid
AND componentid = @componentid, if so u need to specify the UserId also as input

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-15 : 06:28:54

the error would imply you already have that combination of [useraircraftid],[componentid],[usedid],[deleted]

but if you are using getdate() that seems unlikely. are you really using getdate() or in your actual code do you strip it down to just the date etc?

Em

Cralis
Starting Member

11 Posts

Posted - 2008-08-15 : 06:50:52

the delete column is definitly declared as "[deleted] [datetime] NULL,"

There will be a row with the same aircraftid, componentid and usedid... But the 'deleted' column should be making the row unique. I have a few rows that are deleted (That is, have a datetime in Deleted) with the same aircraftid, componentid and usedid... and that's what it must be complain g about. It seems to be ignoring the fact that the uniquness should cover the date column too. The dates are completly different to the currect datetime.. so it's not trying to insert a duplcaite.

This is very strange...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-15 : 06:54:54

quote:
Originally posted by Cralis

the delete column is definitly declared as "[deleted] [datetime] NULL,"

what i mean is, in your update statement do you actually use (as written) GETDATE() or do you pass it from a variable etc

Em

Cralis
Starting Member

11 Posts

Posted - 2008-08-15 : 07:27:53

Oh .. sorry. No, the SP does a SET [Deleted] = GetDate()...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 12:07:55

i guess the problem is you're having more than one record with same [usedid] returned for same useraircraftid AND componentid by the above update and causing getdate() to brand same date to them all.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-15 : 12:38:15

Just to check what I think is obvious.. :)

In your stored proc do you teminate the current record and then insert the new record?

Cralis
Starting Member

11 Posts

Posted - 2008-08-15 : 15:05:34

No... I just update the record I am setting as deleted.

Eg.

I insert a record.
Aircraftid = 1,
Component = 1
usedid=1,
deleted=NULL

I then delete the record, by updating....
ARG!!! I see the problem!!!!

I'll explain...

I then delete the record by updating the deleted flag, where component=1, aircraft=1, usedid=1... setting deleted =GetDate()...

so now we have

Aircraftid = 1,
Component = 1
usedid=1,
deleted=01/01/2008 02:45.23

I then decide to re-add the component to the aircraft.. so.. new record...

Aircraftid = 1,
Component = 1
usedid=1,
deleted=NULL

All cool, because the uq covers all fields, and the previous date keeps it unique in the deleted record.

Now, the problem. I decide to delete the component again, so I call:
UPDATE dbo.[UserAircraftComponent]
SET [Deleted] = GETDATE()
WHERE useraircraftid = @useraircraftid
AND componentid = @componentid

Bang!!

Problem is, it's trying to update BOTH rows with the same date....
Damn... I need to pass the 'id' of the row. NOT the component and aircraftid, as they are not unique.

Damn.. silly error. Easy to fix though.. but.. silly by me... :)

Thanks for the help though guys!

Cralis
Starting Member

11 Posts

Posted - 2008-08-15 : 20:49:28

ACTUALLY... I just need to add one line to my SP, I think!
If my SP looks like this:

UPDATE dbo.[UserAircraftComponent]
SET [Deleted] = GETDATE()
WHERE useraircraftid = @useraircraftid
AND componentid = @componentid
AND [Deleted] IS NULL <----------New line

that should cover it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources