Edwin A. Epstein, III wrote:

>I have 3 fields in a table:
>
>PHONE DOUBLE PRECISION
>D_STATE INTEGER
>LOG_ID INTEGER
>
>Now there is a primary key on PHONE and LOG_ID together. This allows me to
>create multiple lists of phone numbers, but to not have more than one phone
>number in any single list.
>
>every time a phone number is in "use" the d_state is equal to 0. Any
>D_STATE value other than 0 is not in use.
>A phone number should not be in use more than once at any given time.
>
>The following situation is okay:
>
>PHONE,D_STATE,LOG_ID
>7025551212,0,4000
>7025551212,1,3000
>7025551212,1,2000
>7025551212,-3,1000
>
>This situation is NOT:
>
>PHONE,D_STATE,LOG_ID
>7025551212,0,4001
>7025551212,0,4000
>7025551212,1,3000
>7025551212,1,2000
>7025551212,-3,1000
>
>...
>I cannot just create a unique index in addition to the PK since as you can
>see it is possible for the D_STATE to have 2 values of "1". They both
>indicate the phone number was no longer in use on the list.
>
>I beleive if I could create a constraint that would not allow 2 D_STATE
>fields to share the specific value of "0" while sharing the same PHONE
>number that would give me my safety.
>
>If this can be done with a check constraint I would sincerely appreciate
>your help! :)
>
>
>
>

Is there any inconvenience in using a trigger? Something Like

CREATE TRIGGER TRG_CHECK_STATE_BIU0 FOR <TABLE_NAME>
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
IF (EXISTS (SELECT 1 FROM <TABLE_NAME> WHERE PHONE=NEW.PHONE AND
S_STATE=0 AND LOG_ID<>NEW.LOG_ID)) THEN
EXCEPTION "Phone already in use!";
END