15.00 - Logical Integrity Constraints - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Logical Integrity Constraints

The pursuit of theory is frequently perceived as being unnecessary in the day‑to-day practice of administering relational database management systems, but a small dose of theory is essential to understanding the issues you encounter when designing and maintaining databases. If you are interested in pursuing the logical foundations of database integrity constraints, the paper by Godfrey et al. (1998) is a good overview of the subject, though it is grounded in the theory of logic databases rather than ordinary relational systems. Also see the books by Date (2007) and de Haan and Koppelaars (2007).

Concepts that otherwise appear to be complicated and confusing turn out to be rather obvious once the underlying principles have been mastered. As an anonymous wit once observed, “the gap between theory and practice is not as wide in theory as it is in practice.” In other words, without understanding the formal building blocks of relational technology, it is easy to land oneself in considerable trouble in real world practice.

The extent to which practical relational database management can be formalized using the principles of set theory and formal logic is seldom appreciated. The correspondence between relational set theory and relational database theory is not always direct. While it is true that relational database theory is based on the relations of set theory, it necessarily introduces the additional constructs listed below. 

  • Database relations are typed, while set theory relations are not. Note that data types, or domains, are also a form of constraint.
  • Database relations are not ordered left-to-right, while set theory relations are.
  • Dependency theory is critical to database relations, while no corresponding theory exists for set theory relations.
  • Relation variables are critical to database relations, while no corresponding theory exists for set theory relations. As a result, the concept of integrity as it is understood for relational database management is foreign to set theory relations.
  • When an everyday practice such as database management can be abstracted to such a degree, it becomes trivial to determine whether a particular operation against the database is logically correct or not. The importance of this capability is far from trivial. Indeed, it has been argued that all logical differences are big differences, and this contention is surely true with respect to any database upon which the correctness of decision making for an enterprise relies, because if the information in the database is not correct, it is not just worthless, it seriously endangers the viability of the enterprise it is designed to support.

    This principle has been attributed to the philosopher and logician Ludwig Wittgenstein, though the remark appears to have been made to Wittgenstein’s colleague Peter Geach in a private communication, not in a public forum or a published text. Even so, its truth is unassailable. Hugh Darwen has added the following important corollary to this principle: “All logical mistakes are big mistakes” as well as the following additional conjecture: “All non‑logical (psychological) differences are small differences.” (see, for example, Date, 2002; Darwen, 2004). Also see Halpern et al., 2001 for a concise review of the importance of logic in the development of computer science. Section 3 of their paper is called “Logic as a Database Query Language.”