I have a table named PATIENTS defined (short version) as:
SEQ INT AUTO-INCREMENT PRIMARY KEY
SOURCE_SEQ INT (FOREIGN KEY)
This table a a unique index defined as (SOURCE_SEQ, ID, STATUS).
I want to use the STATUS column to define if the patient is online, offline or in conflict.
I have defined the rules as follow:
- if the patient is online, then STATUS is zero (only one patient with the same id from the same source can be online)
- if the patient is deleted, then STATUS is equal to SEQ (can have several deleted patient of the same id in the same source)
- if the patient is in conflict (same id but different name for ex), then STATUS is equal to -SEQ (can have several patient in conflict having the same id in the same source).
My problem is that I don't know how to solve the third rule. I need to make an INSERT SQL COMMAND that will set the STATUS value equal to -SEQ. Is there a way to write this?