How, by using primary and foreign key relationships, the integrity of a database can be maintained
Answers
application or the stored procedure’s logic. Or, you can choose not to enforce a relationship. Choosing not to enforce referential integrity can yield better database performance because SQL Server doesn’t necessarily need to fetch data from a disk for validation. However, you run the risk of violating referential integrity. If you use constraints or triggers, you don’t need to worry about violating referential integrity. So, the choice is simple: If you don’t have special reasons to enforce integrity in your application—or not enforce it—you can choose between constraints and triggers.
Constraints define rules that enforce data integrity. Constraints are simple to maintain and, unlike other mechanisms, they are proactive. In other words, constraints occur before a change operation occurs. But constraints aren’t useful in situations where you need to maintain complex logic, such as when you’re conducting complex calculations, checking data in other tables, or cascading changes to other tables. You can use triggers to complete these tasks, but triggers are reactive—i.e., they occur after a change operation occurs. Moreover, triggers are more complex and might incur more processing overhead than constraints.
Your database design will dictate the method you choose to enforce referential integrity. A poorly designed database leads to the use of complex, resource-consuming mechanisms such as triggers. In most cases, if you’re working with a well-designed database, you can use the preferred mechanism, constraints. But different scenarios need different approaches in enforcing referential integrity.
Entity Integrity
Entity integrity defines a row as a unique entity in a table. You can use a single attribute (for example, the OrderID column in the Orders table) to uniquely distinguish a row from other rows, or you can use a combination of attributes (for example, OrderID and PartNo columns in the OrderDetails table).
SQL Server uses the primary key constraint to enforce entity integrity (for more information, see Poolet, "How to Choose a Primary Key," April 1999). A table can have only one primary key. The primary key constraint doesn’t allow duplicate values and doesn’t allow NULLs. It automatically creates an underlying unique index to disallow duplicate values. If you try to create a primary key on a column that allows NULLs, the action will fail and SQL Server will generate a system error.
SQL Server also supports the enforcement of an alternate key, which is another attribute or combination of attributes (column or columns) that uniquely identifies an entity, or a row in a table. SQL Server uses a unique constraint to enforce the alternate key. A table can have multiple unique constraints. Like the primary constraint, the unique constraint doesn’t allow duplicate values, but it allows one instance of NULL. If a unique constraint is defined on a single column, only one NULL is allowed. SQL Server treats the NULL values as equal, although NULLs are not necessarily equal because NULL is another way of representing an unknown value. If the unique constraint is defined on a combination of columns, SQL Server allows any unique combination of NULLs and known values. The unique constraint also creates a unique index to disallow duplicate keys. You can define the unique constraint on a column that allows NULLs. But the standard way to use the unique constraint is to combine it with NOT NULL. The UNIQUE NOT NULL property precedes the primary key constraint. The SQL Server query processor can use the index created by the primary key or unique constraints to get faster access to the data.