Database Reference
In-Depth Information
phone number data type as a varchar(25), then every column that you de-
fine as a phone number will be exactly the same, a varchar(25). As you re-
call from the discussion of domains in Chapter 2, user-defined data types
are the physical implementation of domains in SQL Server. We highly rec-
ommend using user-defined data types for consistency, both during the ini-
tial development and later during possible additions to your data model.
Referential Integrity
We discussed referential integrity (RI) in Chapter 2. Now we look specifi-
cally at how you implement referential integrity in a physical database.
In general, data integrity is the concept of keeping your data consistent
and helping to ensure that your data is an accurate representation of the
real world and that it is easy to retrieve. There are various kinds of in-
tegrity; referential integrity ensures that the relationships between tables
are adhered to when you insert or update data. For example, suppose you
have two tables: one called Employee and one called Vehicle. You require
that each vehicle be assigned to an employee; this is done via a relation-
ship, and the rule is maintained with RI. You physically implement this re-
lationship using primary and foreign keys.
Primary Keys
A primary key constraint in SQL Server works in the same way as a primary
key does in your logical model. A primary key is made up of the column or
columns that uniquely identify the row in any given table.
The first step in creating a PK is to identify the columns on which to
create the key; most of the time this is decided during logical modeling.
What makes a good primary key in SQL Server, and, more importantly,
what makes a poor key? Any column or combination of columns in your
table that can uniquely identify the row are known as candidate keys.
Often there are multiple candidate keys in a table. Our first tip for PK se-
lection is to avoid string columns. When you join two tables, SQL Server
must compare the data in the primary key to the data in the other table's
foreign key. By their nature, strings take more time and processing power
to compare than do numeric data types.
That leaves us with numeric data. But what kind of numeric should you
use? Integers are always good candidates, so you could use any of the int
 
 
Search WWH ::




Custom Search