Database Reference
In-Depth Information
Chapter 7
Constraints
It is important to design databases in a way that makes efficient processing and querying of the data possible. That
by itself, however, it is not enough. We must make sure that the data we get from the database can be trusted. Think
about the Order Entry system, for example. We can query the OrderLineItems table to get the information about
products we sold, but we cannot trust the results unless we know that the table has no orphaned rows, which do not
belong to any orders in our system.
Constraints allow us to declare the data integrity and business rules for the database and have SQL Server enforce
them. They ensure that data is logically correct, help us to catch bugs in the early stages of development, and improve
the supportability and performance of the system. Let's take a look at the different types of constraints in more detail.
Primary Key Constraints
Conceptually, database design can be separated into logical and physical design stages. During the logical database
design stage, we identify the entities in systems based on business requirements, and we define the attributes and
relations between them. After that, during the physical database design stage, we map those entities to the database
tables, defining data access strategies through the indexes and design the physical data placement across different
filegroups and storage arrays.
Even though the logical and physical database design stages are often mixed together, conceptually they are
separate from each other and can even be performed by different teams, especially on large projects.
Primary key constraint s define the attribute or set of attributes that uniquely identify a record in an entity, or
in the physical database design scope; that is, a row in a table. Internally, primary key constraints are implemented
as unique indexes. By default, SQL Server creates a primary key as a unique clustered index, although it is not a
requirement. We can have non-clustered primary keys or we can even have tables with no primary keys at all.
As you have probably already noticed, the first part of this topic did not mention primary keys, and it routinely
used clustered indexes instead. This was done on purpose. Primary keys conceptually belong to the logical database
design domain, while clustered and non-clustered indexes are the part of the physical database design.
Database professionals, however, often mix the two by defining the clustered indexes as primary keys, even
though, in some cases, it is incorrect from a logical design standpoint. For example, consider an Order Entry system
with an Orders table with OrderId identity column. This column uniquely identifies the order row, and it would be a
perfect candidate for a primary key constraint. Whether it is a clustered or non-clustered primary key depends on the
other factors, of course. In the end, we will have something similar to the code shown in Listing 7-1.
 
Search WWH ::




Custom Search