PostgreSQL supports the DOMAIN
specification, from SQL 2011 working draft spec,
A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists of a data type, possibly a default option, and zero or more (domain) constraints.
This allows us to do really cool stuff like implement a domain for the HTML5-spec for email over a case-insensitive text type. It ensures that all clients accessing the database have an integrity check on the data inserted.
CREATE DOMAIN email AS citext
CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );
Does SQL Server support any such functionality outside of the trigger system?
Best Answer
SQLCLR presents a surface area for creating fully custom data types. In fact, that is how SQL Server supports geometric and hierarchy data types.
Since SQLCLR is based on the Microsoft.Net common-intermediate-language, a large variety of constraints are possible for a SQLCLR data type. For instance, you could easily ensure an email address is from a valid domain by querying DNS for the MX record as part of the data validation code.
SQL Server can index a CLR UDT as long as
IsByteOrdered:=True
is set. There are a ton of properties like that, which you can change to affect how SQL Server uses the UDT. For equality matching like that required by an index, SQL Server simply looks at the binary value stored in the page i.e. it doesn't need to look at the UDT code at all.As an example of a SQLCLR User-defined-type that checks a domain-space for validity, I've written the following terrible proof-of-concept VB.Net code:
Since the code above is not signed, you should test it only on a development machine where you can enable the
TRUSTWORTHY
database setting. Once the code is compiled, you import it into SQL Server via the following:The UDT can then be used like this:
The above code returns:
However, when attempting to insert an address belonging to a non-existent email domain, as in:
You see an error: