1. 8
  1.  

  2. 1

    I honestly haven’t been following this series and mostly scanning it, but..

    a check constraint solves this either/or situation. Even stackoverflow figured it out: https://dba.stackexchange.com/questions/284581/in-postgresql-how-do-i-make-it-so-that-either-column-a-or-column-b-must-be-non

    1. 5

      check constraint unfortunately can only handle the most trivial case: two or more scalar fields, all in the same table. The use case that I use as an example takes (at least) two tables, with M:N relation. I would be happy to be mistaken, but afaik check constraints do not support subqueries.

      Also, there are databases other than Postgresql. For example, MySQL still (at the version 8.0+) ignores the check constraint. :)

      It makes me sad that people are so burned out by the quality of content on the Internet that they assume that somebody’s 1000+ word article could be entirely replaced with the “check constraint solves this situation” statement.

      The idea of this series is to dive deep into all sorts of connections between logical modeling, relational modeling, physical aspects of data storage, development practices, parallels with data modeling in software, stakeholder concerns, knowledge management, etc., etc. Either/or example seems to be a sweet spot where complexity is very un-trivial yet very manageable for the discussion.

      Thank you,

      1. 1

        afaik check constraints do not support subqueries.

        In many DBMSs (including PostgreSQL among the open-source ones), you may invoke a user-defined function returning a boolean in your check clause, which allows you to do basically anything.

        1. 1

          Okay, I think Postgresql 13 docs are a bit misleading: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS (blue “Note box at the end of the section”):

          PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. PostgreSQL assumes that CHECK constraints’ conditions are immutable, that is, they will always give the same result for the same input row. (and the rest of the box, actually).

          I’m not sure how to read this, really. I understand that you can create a UDF that will receive the values of the current row, but it can do the subquery at your will. However, it’s not clear if it’s going to see the changes made by a different query, because of “immutable” clause.

          Could you check if that’s actually the case? E.g. I want to have a constraint that if cnt field equals N then there are exactly N rows in a different table. Would it work?

          Thank you!

          1. 1

            Well, no. Constraint-check clauses are not triggered upon deletion, so you won’t be able to sync the counter in that case.

            Triggers are usually used for that. Maybe this page will help:

            https://wiki.postgresql.org/wiki/Count_estimate

            (See also the links at the bottom of the page).

        2. 1

          OK I totally agree with you. Except I’ll try to answer: laziness! I certainly am lazy. I look forward to the next in the series now that I know why check isn’t strictly useful in this case.