Check constraints on Tables within UDFs - cannot drop constraint l

This is probably obscure usage of the SQL Server feature-set, but any help
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:

> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com