I have one table like below Test table. My requirement is to create constraints to confirm <g class="gr_ gr_331 gr-alert gr_gramm Grammar only-ins replaceWithoutSep" data-gr-id="331" id="331">uniqueness</g> of STID value 101 with LN.

like 

ID - LN - STID

1 - 'ABC' - 101  ---- Valid Row

2 - 'ABC' - 202 --- Valid Row

3 - 'ABC' - 202 --Valid Row (as I want only unique when LN = 'ABC' with STID = 101)

4 - 'ABC' - 101 -- Invalid Row (As I want uniqueness base on LN and STID = 1011)

create table dbo.Test

(
ID int identity,
LN varchar(50),
STID bigint
)

Is this possible with constraints as I don't want to use <g class="gr_ gr_1041 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1041" id="1041">trigger</g>.

Thanks

Hi WebRelations,

I'm not an expert in constraints, but I think this will not be possible. Your requirement seems to be business logic and no "data-logic", so I would let my business logic take care of this.

Greets

You can have a filtered unique index on columns LN and STID, restricted to STID = 101 (or whatever your rules are for uniqueness).

If I understand correctly, you want to allow only one STID 101 value per LN.  In that case, you can use a unique filtered index instead of a unique constraint:

CREATE UNIQUE NONCLUSTERED INDEX idx_Test_STID_101 ON dbo.Test(LN, STID) 
WHERE STID = 101;

Thanks!