CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 197

be called idx_customer_name. The idea is to provide enough detail about
the purpose of the index without the name getting out of hand. It is sim-
ple enough to use tools or queries to look up which columns are in the
index.
Naming User-Defined Data Types
User-defined data types (UDTs) can be quite useful when you're trying to
enforce consistency in similar data across multiple objects. If you choose to
use UDTs, we suggest you name them in a way that makes sense to you—
no special prefix, only a good descriptive name. For example, you may have
multiple tables that contain an order number column. If you want to en-
sure that all of those columns have exactly the same format and type, you
could create a UDT called ordernum to enforce the required data type.
Naming Primary Keys and Foreign Keys
When we name our primary keys, we simply start with “PK_” and end with
the table name. For example, your customer table called tbl_customer
would contain a primary key called PK_tbl_customer. We keep the “tbl”
prefix in the name to avoid confusion.
Foreign keys are a little trickier because they commonly exist in one
table and reference another. As you may have guessed, we start foreign
keys with an “FK_” prefix. After that, the name has two parts: the first is
the referencing table, and the second is the referenced table. For example,
suppose we want to name the foreign key on the order detail table named
tbl_order_detail that references the order table tbl_order. We would name
it FK_tbl_order_detail_tbl_order. We again leave the “tbl_” prefixes in the
name of the foreign key. This helps to avoid confusion by offering a clear
separator for the referencing and the referenced table.
Naming Constraints
Defaults and constraints are straightforward. Defaults are named with a
“DF_” prefix followed by the table and then the column on which the de-
fault exists. For example, if the customer table has a column named status
with a default value, we name our default constraint DF_tbl_customer
_status. For check constraints, we start with a “CK_” prefix and follow with
a meaningful name. If you want to ensure that an entity contains at least
one phone number, you might write a check constraint. The name for this