sql server - Prevent a value from being entered if it's a prefix of another value -

how prevent value being entered prefix of value in same column? example, if mytable.numberprefix contains abc ab can't added.

my first attempt (below) use indexed view. unique index cannot created on view uses derived table (and can't figure out how write view without it).

create view mytable schemabinding select   left(a.numberprefix, b.length) commonprefix    dbo.mytable   cross join   (     select distinct       len(numberprefix) length           dbo.mytable   ) b  create unique clustered index myindex on mytable (commonprefix) --error 

some test data:

insert mytable (numberprefix) values ('abc')  -- ok insert mytable (numberprefix) values ('ab')   -- error insert mytable (numberprefix) values ('a')    -- error insert mytable (numberprefix) values ('abd')  -- ok insert mytable (numberprefix) values ('abcd') -- error 

use check constraint user defined function:

create function fnprefix(@prefix varchar(100)) returns bit begin     if (select count(*) mytable          mycolumn @prefix + '%' or @prefix mycolumn + '%') > 1         return 0      return 1 end 

then add constraint:

alter table mytable add constraint  chkprefix check(dbo.fnprefix(mycolumn) = 1)