Relational Databases 101—Using User-Defined Types, Rules, and Defaults

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

Using User-Defined Types, Rules, and Defaults

In SQL Server, non-CLR UDTs are pretty straightforward—they're simply aliases to the base types. This way, you can define a UDT for PostalCode (based on a varchar(11) and specify the PostalCode UDT when the table is created. Once defined, a UDT can be assigned a global default. That is, when a new row is added to the table and no value is supplied, SQL Server substitutes the registered default for the column value and any other columns defined with the UDT.

In a similar manner, you can also define SQL Server rules or (better yet) check constraints for specific columns or to UDTs, as I discussed in Chapter 2. These constraints are used to implement your business rules—they define what's permissible in a specific column and what's not. For example, you know (based on how you run your business) that customer discounts can range from 0% to 15% and correct shipping delays are between 1 and 90 days. Setting up SQL Server rules to enforce these business rules is fairly simple—check constraints are a bit harder. Both rules and constraints can be any expression valid in a WHERE clause and can include such elements as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). However, the constraints cannot reference columns or other database objects. Let's walk through the process of creating a new UDT (alias) and associated check constraints.

Start by creating a new User-Defined data type in the database by using the SQL Server Management Studio wizard that starts when you right-click on User-defined Data Types | New User-defined Data Type, as shown in Figure 3.5.

Figure 3.5 Creating a new User-Defined data type.

All I have to do is fill in the form, as shown in Figure 3.6. Here, you provide the UDT name, base datatype, and length. You can also specify that the UDT can be set to NULL. Later, I'll use this same dialog to set the default value and the rule/check constraint for this type.

Figure 3.6 Creating a new UDT based on the varchar datatype.

Next, I create a new constraint for our PostalCode UDT, as shown in Figure 3.7. Again, right-click the Constraints item under the selected table.

Figure 3.7 Adding a New Constraint for the PostalCode UDT.