samedi 25 avril 2015

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

How could I prevent a value from being entered that is a prefix of another value in the same column? For example, if MyTable.NumberPrefix already contains abc then ab can't be added.

My first attempt (below) was to use an indexed view. But a unique index cannot be created on a view that uses a derived table (and I can't figure out how to write the view without it).

create view MyTable
with schemabinding
as
select
  left(a.NumberPrefix, b.Length) as CommonPrefix
from 
  dbo.MyTable a
  cross join
  (
    select distinct
      len(NumberPrefix) as Length
    from
      dbo.MyTable
  ) b

create unique clustered index MyIndex on MyTable (CommonPrefix) --ERROR

Aucun commentaire:

Enregistrer un commentaire