Database Reference
In-Depth Information
that it can be hard to find the data that violated the constraint. In this case,
it's pretty easy to spot a negative number, but imagine if the constraint were
more complex and contained more columns. You would know only that
some column in the constraint was in violation, and you would have to go
over your data to find the problem. On the other hand, we could have cre-
ated a constraint for each column, making it easier to track down problems.
Which method you use depends on complexity and personal preference.
Implementing Referential Integrity
Now that we have covered PKs, FKs, and constraints, the final thing we
need to discuss is how to use them to implement referential integrity.
Luckily it's straightforward once you understand how to create each of the
objects we've discussed.
One-to-Many Relationships
One-to-many relationships are the most common kind of relationship you
will use in a database, and they are also what you get with very little addi-
tional work when you create a foreign key on a table. To make the rela-
tionship required, you must make sure that the column that contains your
foreign key is set to not allow NULLs. Not allowing NULLs requires that
a value be entered in the column, and adding the foreign key requires that
the value be in the related table's primary key. This type of relationship im-
plements a cardinality of “one or more to one.” In other words, you can
have a single row but you are not limited to the total number of rows you
can have. (Later in this chapter we look at ways to implement advanced
cardinality.) Allowing NULL in the foreign key column makes the rela-
tionship optional—that is, the data is not required to be related to the
reference table. If you were tracking computers in a table and using
a relationship to define which person was using the computer, a NULL
in your foreign key would denote a computer that is not in use by an
employee.
One-to-One Relationships
One-to-one relationships are implemented in exactly the same way as one-
to-many relationships—sort of. You still create a primary key and a foreign
key; the problem is that at this point SQL Server still allows users to insert
many rows into the foreign key table that reference the primary key table.
Search WWH ::




Custom Search