0

I've written a function that returns int Still, I can't use it in CHECK constraint, this error is output:

'myFunction' is not a recognized built-in function name.

create table MyTable(
attr varchar(100) CHECK(myFunction(attr)=1)
);

I also tried

create table MyTable(
attr varchar(100)
);
alter table MyTable
add constraint CheckAttr
CHECK(myFunction(attr)=1);

I saw people wrote that it is not possible to invoke user defined functions in CHECK constraint, but here it's written it is possible:

CHECK constraints User-defined functions that return scalar values can be invoked in CHECK constraints if the argument values passed to the function reference columns only in the table or constants. Each time the query processor checks the constraint, query processor calls the function with the argument values associated with the current row being checked. The owner of a table must also be the owner of the user-defined function invoked by a CHECK constraint on the table.
Orif Khodjaev
  • 1,080
  • 1
  • 13
  • 34
  • 1
    You have to use the schema, as NoDisplayName has shown. However, I'd also caution that the combination of check constraint and UDF is usually done to try to implement multi-row or multi-table constraints and are easy to get wrong for corner cases - if you could show the actual function and explain what's needed, we may have a better overall suggestion. – Damien_The_Unbeliever Feb 03 '15 at 07:40
  • Well, I know it is not generally a good idea, but I was asked to do this: the function checks validity of email. – Orif Khodjaev Feb 03 '15 at 07:44

1 Answers1

4

You need to use schema name when you are calling scalar function

create table MyTable(
attr varchar(100) CHECK(schema_name.myFunction(attr)=1)
);

As mentioned by Damien_The_Unbeliever there are drawbacks in using UDF in check constraint for more info check here

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172