| Author |
Topic |
|
charlemagne
Starting Member
3 Posts |
 Posted - 2010-03-30 : 22:56:21
|
| I have a table with a definition like this:ID int autonumber PKName nchar(30)Deleted bit... some other fields ...I would like to have no more than one active record (Deleted = 0) with a given name, but any number of inactive records with any given name. If someone deletes a record (marks it inactive, not actually using SQL DELETE), and then someone else creates a new record with the same name, there should be no problem. There should only be a constraint violation if there is already another record with Deleted = 0 and the same name.I can see two ways of doing this:1) have two name fields: main [unique] and backup [not unique], nulling the main field when the record is marked inactive, and copying back from the backup if the record is marked active again2) have two tables, moving deleted records into the historical entries tableBoth of these feel hackish -- does anyone know of a better way? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2010-03-31 : 07:14:24
|
| How about an "Active" table that has a FK to the original table's PK and a unique constraint on the name? You would then assume everything in the main table was deleted unless it had a corresponding entry in the active table. No triggers, no locks & proper RI.You can create a view to the main table joined to the active table to make life easier in day to day use. |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-31 : 07:28:49
|
| How about replacing the Deleted column with a VersionNo (INT) column. VersionNo = 0 means it's the current record. Anything else means the record has been deleted. When you add a record with Name = 'John', the VersionNo will be 0. To delete the 'John' Record, simply find the maximum VersionNo for all 'John' Records, add one to it, then update the current 'John' record with that value. This offers 2 advantages:1 - You can have a unique index on the Name, VersionNo columns2 - You'll be able to see the order in which the records were deleted. Another way to do it would be to have a DeletedDate column. If it's NULL, then it's the current record. To delete a record, update it with the GETDATE(). This offers the same advantages as the first, but also allows you to see exactly when each record was deleted. However, it will use an extra 4 bytes per row over the first method.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2010-03-31 : 07:51:19
|
| a) It's the same problem setting versionNo to 0 as having a deleted flagb) max will allow duplicates when 2 users update at the same time |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-31 : 08:02:49
|
quote:
Originally posted by LoztInSpace
a) It's the same problem setting versionNo to 0 as having a deleted flag
If the problem is that there's not way to specify a constraint to stop 2 records both being active, because there will be multiple inactive (Deleted = 1) records, then a VersionNo solves that problem, because it allows a unique constraint/index to be places on the 2 columns in question. Each deleted record has it's own VersionNo. quote:
b) max will allow duplicates when 2 users update at the same time
That's what locks are for.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
byrdzeye
Starting Member
14 Posts |
Posted - 2010-04-09 : 14:06:20
|
| feels hackish...To implement a unique constraint you have to implement a number of tables, processes. I don't think the constraint belongs here.Just implement the 'business rule' in a trigger that returns a violation error if needed. Nothing else needed. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 14:37:17
|
quote:
Originally posted by byrdzeye
feels hackish...To implement a unique constraint you have to implement a number of tables, processes. I don't think the constraint belongs here.Just implement the 'business rule' in a trigger that returns a violation error if needed. Nothing else needed.
Hackish? I'd call using a trigger to implement a unique constraint "Hackish". I'm not sure what you mean by "you have to implement a number of tables, processes". Nothing else needed? How about an index to make the trigger run more efficiently? While you're at it, why not make it a unique trigger, and then hey, guess what, the trigger in now redundant.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
Enforce constraint based on more than one record