How to create a constraint on custom datatype in SQL Server-CodePudding

I want to create a custom datatype in SQL Server with some sort of constraint that checks and validates the type by running through a custom function and returns an error if it fails.

For example

I have some sort of a Hebrew calendar in SQL Server, with a function that converts from a Hebrew date to a Gregorian date, so I tried to make a custom datatype for example:

create type dbo.HebrewDate
from nvarchar(20)

This would work as intended, but I wanted to have a constraint like:

constraint <constraint_name> check((select <my_convert_function>(HebrewDate)) is not null)

But this doesn't work, as create type as itself doesn't support constraints.

So I tried to create a type that would return a table:

create type dbo.HebrewDate
as table(
    HebrewDay nvarchar(4),
    HebrewMonth nvarchar(10),
    HebrewYear nvarchar(6),
    constraint <constraint_name> check((select <my_convert_function>(HebrewDay, HebrewMonth, HebrewYear)) is not null)
)

This returns the following error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

And when I remove the select clause it returns:

Incorrect syntax near the keyword 'CONSTRAINT'.

I found a solution in mssqltips.com that might work, but as I use azure-data-studio and not ssms this solution doesn't work for me, as when I right click on Types it only shows Refresh.

Another problem with the solution above, would be, that he's using create rule, while on docs.microsoft.com it says on create rule:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE. For more information, see Unique Constraints and Check Constraints.

Any suggestions? I would want to create it in sql, as I don't yet know how to program in c#, but if that's the only option...

My tables and functions are in Github

CodePudding user response：

You cannot have constraints on alias types, and they are therefore mostly useless.

The grammar for CREATE TYPE specifies column and table CHECK constraints for table types, but they cannot be named. You also cannot use a subquery, but normal CHECK constraints also cannot, and I don't see why you would want to.

create type dbo.HebrewDate
as table(
    HebrewDay nvarchar(4),
    HebrewMonth nvarchar(10),
    HebrewYear nvarchar(6),
    check(<my_convert_function>(HebrewDay, HebrewMonth, HebrewYear) is not null)
)

Whether you should be using a scalar function here is a different question entirely. They have serious performance implications, especially when used in CHECK constraints.

A much better option would be a SQLCLR type that returns the date, taking the information directly out of HebrewCalendar, which avoids you having to reinvent the wheel. _{1

1 קידוש החודש is really difficult}