Prevent a value from being entered if it is a prefix of another value
How can I prevent entering a value that is a prefix of another value in the same column? For example, if it MyTable.NumberPrefix
already contains abc
, then ab
it cannot 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 a 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
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
+3
source to share
1 answer
Use check constraint
with user defined function
:
create function fnPrefix(@prefix varchar(100))
returns bit
as
begin
if (select count(*) from MyTable
where MyColumn like @prefix + '%' or @prefix like MyColumn + '%') > 1
return 0
return 1
end
Then add a constraint:
alter table MyTable
add constraint chkPrefix check(dbo.fnPrefix(MyColumn) = 1)
+1
source to share