sql - Unique constraint calling user defined function -

sql - Unique constraint calling user defined function -

i have 2 tables:

create table primarytable ( value nvarchar(50) not null primary key type not null ) create table secondarytable ( value nvarchar(50) not null, synonym nvarchar(50) not null, constraint pk_secondarytable primary key (value, synonym) )

so, there can many synonyms each value. want deny adding more synonyms if synonym+gettypeforvalue(value) not unique.

so e.g.

primarytable: ------------- value | type -------------|--------- toyota f-150 | carmake gmc canyon | carmake secondarytable: --------------- value | synonym -------------|--------------- toyota f-150 | toyota pickup gmc canyon | gmc pickup

is ok, but

primarytable: ------------- value | type -------------|---------------- toyota f-150 | carmake gmc canyon | carmake secondarytable: --------------- value | synonym -------------|----- toyota f-150 | pickup gmc canyon | pickup

should not ok (synonym + value's associated type not unique).

i tried making function

create function uf_gettypeforvalue(@value nvarchar(50)) returns nvarchar(50) begin homecoming (select type primarytable p p.value = @value) end

and add together unique constraint secondarytable:

alter table secondarytable add together constraint uc_secondarytable_synonym_unique_for_type unique (synonym, uf_gettypeforvalue(value))

however, error message on incorrect syntax near '('.

i have sucessfully called functions check-constraints before, apparently can't unique constraints?

is there syntax trick need know or how can accomplish way?

from syntax here, think can columns unique constraint. however, see "check" constraint can contain logical expression. utilize accomplish it. create row count query within function so:

--drop function numberofsimilarrows create function numberofsimilarrows(@synonymtocheck nvarchar(50), @valuetocheck nvarchar(50)) returns int begin declare @rowcount int select @rowcount = count(*) secondarytable synonym = @synonymtocheck , value = dbo.uf_gettypeforvalue(@valuetocheck) homecoming @rowcount end;

then can utilize in check constraint so:

alter table secondarytable add together constraint uc_secondarytable_synonym_unique_for_type check (dbo.numberofsimilarrows(synonym, value) <= 1 );

sql sql-server tsql sql-server-2012 unique-constraint