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