Saturday, September 4, 2021

Understanding Relational Constraints

“The data in a relational database is stored in form of a table. A table makes the data look organized. Yet in some cases we might face issues while working with the data like repetition. We might want enforce rules on the data to avoid such technical problems. Theses rules are called constraints. A constraint can be defined as a rule that has to enforced on the data to avoid faults. There are three kinds of constraints: entity, referential and semantic constraints. Listed below are the differences between these three constraints:
1. Entity constraints -- primary key, foreign key, unique, NULL -- are posed within a table and used to enforce uniqueness and to define no value [respectively].    
2. Referential constraints -- foreign key -- are enforced with more than one table for referring other tables for analysis of the data.
3. Semantic constraints -- datatypes -- are  enforced in a table on the values of a specific attribute and help the data segregate according to its type. Example: name varchar2(30).”
Before we tackle the main subject, let's get some misconceptions out of the way. As we have explained so many times:

  • Data is not "stored in a form of a table" -- it can be stored in any number of physical formats, at the discretion of DBMS designers and DBAs. Physical independence is a core advantage of the RDM.
  • A table does not "make the data look organized". Data is by definition organized -- be it relationally or not -- otherwise it would be random noise not data.  A database relation can be visualized as a R-table, but tables do not play any role in RDM.
  • While some "repetition" (i.e., redundancy) is prevented by constraints (e.g., uniqueness), others are avoided by database design (e.g., 5NF DB relations).

And now to constraints.


DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.


- 08/31 TYFK: Normalized, Fully Normalized, Non-Normalized, Denormalized -- Clearing the Mess

- 08/13 OBG: The Myth of Market Based Education

- 08/05 TYFK:Facts, Properties, Relationships, Domains, Relations, Tuples

- 07/22 Documents and Databases

- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the
FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with the that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my columns there no longer work. I moved only the 2017 columns to dbdebunk, within which only links to sources external to AllAnalytics may work or not.

I deleted my Facebook account. You can follow me:
- @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.
- The PostWest blog for monthly samples of global Antisemitism – the only universally acceptable hatred left – as the (traditional) response to the existential crisis of decadence and decline of Western  civilization (including the US).
- @ThePostWest on Twitter where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.



A relational database represents a conceptual model consisting of objects with properties and relationships. Properties-object modeling (PoM) is grounded in Ontological Commitment to Properties (OCP) according to which an object is a named collectiion of co-ccurring properties.

  • Objects:

- primitive: entities;
- compound:
  * entity groups;
  * multigroup.

  • Properties:

- individual: of entities[1];
- collective:
  * of groups[3];
  * of multigroup[4].

  • Relationships:

- among entity properties[2];
- among entities[3]:
- among-groups[4].

Properties fall into four categories:

  • 1st order properties (1OP): direct properties of individual entities [1] ;
  • 2nd order properties (2OP): relationships among 1OPs are indirect properties of entities [2] ;
  • 3rd order properties (3OP): relationships among entities within the same group are collective properties of the group [3];
  • 4th order properties (4OP): relationships among groups are collective properties of the multigroup [4] .

In a relational database:

  • Domains represent properties;
  • Relations represent entity groups;

- attributes represent properties in the context of a specific group;
- tuples (sets of attribute values) represent facts (values of entity properties).

Constraints ensure consistency of the database with the conceptual model, which is why they are all semantic. There are five categories of relational constraints:

  • Domain constraints
  • Attribute constraints (1OPs)
  • Tuple constraints (2OPs)
  • Multi-tuple constraints (3OPs)
  • Multi-relation constraints (4OPs).


Business rules jointly specify the conceptual model in natural language and constraints are their formal database representation expressed in a data sublanguage. As such they are all semantic -- they do not prevent "technical problems" or "faults", but inconsistencies of the database with the conceptual model (misrepresentation thereof).

"Entity constraint" reflects common confusion of levels of representation: entity is conceptual, constraint is logical. Moreover, this terminology suggests that uniqueness and referential constraints are tuple constraints in our relational classification. But just because PKs/CKs and FKs are declared within the table definition in SQL is not a sound basis for lumping them together. This is obvious from the qualification that "referential constraints are enforced with more than one table for referring other tables" (what "analysis of the data"????). In fact:

  • Uniqueness constraints are multi-tuple (why?) and represent 3OP rules;
  • Referential constraints are multi-relation and represent 4OP rules.

We follow Codd (not Date and Darwen) and distinguish between database domains and programming datatypes (SQL does not support relational domains). Be that as it may,

  • Why are only domain constraints semantic?
  • What about the rest of relational constraints? Clear confirmation of lack of foundation knowledge in the industry: most practitioners know only SQL and are unaware of them, which explains all the absurd claims that the RDM does not support relationships.

We close with a rare online pronouncement that is actually correct:

“Because of the resemblance of SQL tables to relations, terms that involve relations get sloppily applied to tables. But although you can borrow terms and give them SQL meanings -- value, table, FD (functional dependency), superkey, CK (candidate key), PK (primary key), FK (foreign key), join, and, predicate, NF (normal form), normalize, 1NF, etc -- you can't just substitute those SQL meanings for those words in RDM definitions, theorems or algorithms and get something sensible or true. Moreover, SQL presentations of RDM notions almost never actually tell you how to soundly apply [them] to a SQL database. They just parrot RDM presentations, oblivious to whether their use of SQL meanings for terms makes things nonsensical or invalid.”


Semantics, Relations and the Missed Link: Constraints

What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency

Relationships and the RDM

Levels of Representation: Relationships, Rules, Relations and Constraints

Integrity Is Not Only Referential: DBMS vs Application Enforced Constraints

What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency

Third Order Properties and Multi-Tuple Constraints: An Example



No comments:

Post a Comment

View My Stats