Suggestion for sql scenario

Question

I am having two columns in table

InventoryId | RevisionId
-------------------------
1   |   1
2   |   1
2   |   2
2   |   2
3   |   1
3   |   2
3   |   3
3   |   3

but from now on I want to prevent following records

So I thought to create a unique index on these two columns but the table having so much existing data. So anything we can do this situation. Any suggestion?

so the existing data `2 | 2` is allowed but any new rows may not have both the same value ? — GuidoG, Mar 27 '19 at 11:44
Either you first remove existing duplate values and then add your unique key, or you prevent now duplicates with a trigger — GuidoG, Mar 27 '19 at 11:45
@Mano the table having so much of existing data which can be used as history so I can not do that — Ankit Vaidya, Mar 27 '19 at 11:45
Add an other table with all the combinations you don't want to insert again. Check for this table for every insert into the original table, if combination exists then stop inserting. — Srikar mogaliraju, Mar 27 '19 at 11:51
It could be done with a check constraint, check the edit in my answer — GuidoG, Mar 27 '19 at 12:24

GuidoG · Answer 1 · 2019-03-27T12:57:26.180

you can use a trigger to prevent new rows being added with duplicate values

look at this example

create trigger TR_UI_YourTable on YourTable
for update, insert as
begin
     set nocount on

     if exists (select 1 from inserted i where i.InventoryId = i.RevisionId)
     begin
          ;throw 99001, 'no dupes allowed anymore...', 1
     end
end

A better solution would be to move the duplicates to a seperate table for history, and then add a check constraint on these 2 columns

EDIT

you could do it by an check constraint like this

alter table yourtable
add constraint chk_dupes 
  check ((InventoryId <> RevisionId) or (id <= 12345))

where 12345 is the highest value of the column id now.
You will have to test it a bit if it works on all situations.
Also, it will only work if all new rows have a value in id that is larger then the current highest value (12345 in my example)

Suggestion for sql scenario

1 Answers1