0

I have a table called A with these columns:

id, code, unicity

unicity can only have three values 1,2 & 3.

I want to add a unique constraint for a combination of all three columns but with some conditions on unicity.

--first senario:
insert into A values(1,1,1); 
insert into A values(1,1,2); --=>ok

--second senario:
insert into A values(1,1,1); 
insert into A values(1,1,3);--=>not ok

--third senario:
insert into A values(1,1,1); 
insert into A values(1,1,1);--=>not ok

--fourth senario:
insert into A values(1,1,2); 
insert into A values(1,1,1);--=>ok

--fifth senario:
insert into A values(1,1,2); 
insert into A values(1,1,3);--=>not ok

--sixth senario:
insert into A values(1,1,2); 
insert into A values(1,1,2);--=>not ok

When the unicity is 1, if there is already a unicity 1 or 3 with same id and code it should be an error. But if there is already a 2 with same id and code it should be ok. 

create index test on A 
{
id,
code 
case unicity when 1...

}

My question is how to write the case statement for unicity on this case?

Marjan Kalanaki
  • 183
  • 1
  • 4
  • 13
  • Sounds like what constitutes uniqueness is not well defined enough. You will likely need to either put this logic in a stored procedure or in your application code. – Brian Driscoll Mar 29 '17 at 14:52
  • I think you can achieve what you want to do with `trigger`. Maybe [this](http://stackoverflow.com/questions/1012721/validating-update-and-insert-statements-against-an-entire-table) or [this](http://stackoverflow.com/questions/38677473/how-can-i-validate-data-before-insert-update-with-sql-server) can help you – EhsanT Mar 29 '17 at 15:00

0 Answers0