Value dependent User Key

Question

Value dependent User Key

andieharper

Newbie

Points: 3
More actions
May 19, 2010 at 5:41 am

#140459

I have to create a user key to maintain uniqueness in my tables.
For auditing and historic reasons, records are never deleted in any table. Each record has a uniqueidentifier as the primary key and a deleted column which is set to false when the record is live or true when the record is deleted.
The problem I encounter is that is the must only ever be 1 unique live record for a combination of fields (not including the primary key), but there may be multiple deleted records with the same key fields e.g.
/---Unique Key---\
PK Field1 Field2 Deleted
=====================================
1 Value1 Value2 False
2 Value1 Value3 False
3 Value1 Value2 False <-Record is not allowed
4 Value1 Value2 True
5 Value1 Value2 True <-Record is allowed
So
When Deleted = false then the unique key is Field1,Field2
When Deleted = true then the the unique key can be ignored (or set to PK,Field1,Field2)
Is there any way in SQL Server 2005 to create a value dependent user key like this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it looks to me that although logically the unique key is just two columns, in actuality for db purposes, shouldn't it include the deleted column as well?

Field1 Field2 Deleted <---that's really what is unique, according to your rules.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andieharper Newbie Points: 3 More actions · Answer 2

Thanks for the response but merely setting the user key to include the Deleted column does not solve the issue. It is then not possible to have multiple deleted records with the same key and hence breaks the audit trail.

What is needed is userkey to be Field1,Field2 when deleted = false and to be PK,Field1,Filed2 when delete = true.

I have thought of adding another column called Active to the tables being true when the record is live and false when the record is deleted

Thereby I could have

Field1,Field2,Active as my live user key (to maintain uniqueness of Field1, Field2)

PK,Field1,Field2,Deleted as my deleted user key (to allow multiples of Field1,Field2)

That would work, but if I could do it without adding the extra column it would be an administration task rather than development

RP_DBA SSCertifiable Points: 5116 More actions · Answer 3

You could try enforcing it through a trigger. Something like this?

SET NOCOUNT ON

GO

CREATE TABLE dbo.Sample (

PK int identity(1,1)

, Field1 varchar(50)

, Field2 varchar(50)

, Deleted bit )

GO

CREATE TRIGGER dbo.trg_Sample

ON dbo.Sample

INSTEAD OF INSERT

AS

BEGIN

IF EXISTS(SELECT 1 FROM INSERTED WHERE Deleted = 0)

IF EXISTS (SELECT1

FROMINSERTED ins

INNER JOIN dbo.Sample s

ON ins.Field1 = s.Field1

AND ins.Field2 = s.Field2

ANDins.Deleted = s.Deleted

WHEREins.Deleted = 0 )

BEGIN

rollback

raiserror('key violation', 16, 1)

return

END

INSERT INTO dbo.Sample (Field1, Field2, Deleted)

SELECTField1

, Field2

, Deleted

FROMINSERTED

END

GO

INSERT INTO dbo.Sample (Field1, Field2, Deleted) VALUES ('Value1', 'Value2', 0)

GO

INSERT INTO dbo.Sample (Field1, Field2, Deleted) VALUES ('Value1', 'Value3', 0)

GO

INSERT INTO dbo.Sample (Field1, Field2, Deleted) VALUES ('Value1', 'Value2', 0)

GO

INSERT INTO dbo.Sample (Field1, Field2, Deleted) VALUES ('Value1', 'Value2', 1)

GO

INSERT INTO dbo.Sample (Field1, Field2, Deleted) VALUES ('Value1', 'Value2', 1)

GO

SELECT * FROM dbo.Sample

GO

DROP TABLE dbo.Sample

GO

_____________________________________________________________________
- Nate

@nate_hughes

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 4

Instead of using TRUE/FALSE (two states), try using a datetime value for when the row was deleted. You can then use NULL to identify the active row - with a unique constraint across the 3 columns.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

andieharper Newbie Points: 3 More actions · Answer 5

RP_DBA

Good solution with the triggers. We already have Insert/Update triggers for all tables for auditing purposes. It wouldn't be too much extra hassle to add the key check to the generation scripts.

Jeffery

I really like the idea of using a timestamp instead the bit field for deleted, thats does the solution in one hit and will require the minumum recoding.

Thanks for all the help guys