From Newsgroup: comp.databases.theory

Derek,
this is a follow-up to our previous discussion. Since that thread is
already pretty long, and I have a relatively narrow question, I'm
starting a new thread.

The question concerns your model in this document, p. 8:

https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/IDEF1X/Nicola%20IDEF1X%202.pdf

Among the rest, the model declares:

Person is 0-to-1 Employee

The textual predicates do not state it explicitly, but the model also
declares the converse relationship:

Employee is 1 Person

Besides:

Employee is an exclusive basetype, one of {FullTime, Consultant}

Your predicates do not state is explicitly, but the verbal phrase for
exclusive subtyping is "is". So, the model also declares:

EmployeeFullTime is 1 Employee
EmployeeConsultant is 1 Employee

"Each Basetype and Subtype pair should be perceived as a single logical
unit" (same doc., p. 5). So, it's fair to say, e.g., that an
EmployeeFullTime has a JobTitle and an EmployeeDate, and I can sensibly
query for the job titles of all full time employees. JobTitle and
EmployeeDate are descriptors of EmployeeFullTime.

But Person and Employee should not be perceived as a single logical
unit: as per your discussion in -o3, that would be incoherent. So, does
an Employee have a NameLast or a BirthDate? If so, what in the model
does entitle me to state that?

For comparison, suppose that there is also a Hunting License entity, and
a relationship between Person and Hunting License:

Person has 0-to-1 Hunting License

Certainly, I would not say that a Hunting License has a NameLast or
a BirthDate or that NameLast and BirthDate are descriptors of Hunting
License.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Nicola

On Thursday, 10 June 2021 at 19:25:02 UTC+10, Derek Ignatius Asirvadem wrote:

On Thursday, 10 June 2021 at 02:43:14 UTC+10, Nicola wrote:

1. Lost formatting, retry in a variable-width font, arrgh:
SELECT Name, -- Country.Name
________StateCode,
________Town
____FROM Town T
____JOIN Country C ON
________T.CountryCode = C.CountryCode
____WHERE Town LIKE ( rCLNapoli%rCY, rCLNaples%rCY rCLNeapolit%rCY )
2. I don't see how this is related to Incomplete Specialisation. It is a straight explanation of FOPC Predicates, in the context of the /RM/ and IDEF1X.
3. In general, I would stop using OO/OOP/ORM terms when dealing with data and databases, and specifically use Relationals terms when the database is Relational. Use of their terms implies their meaning, which is crippled, because OO is crippled (ambiguous; not a Standard; 420 notations; nothing clearly defined; etc). I deal with OO/OOP/ORM people all the time, and they appreciate that I do NOT use their terms, that I use Relational terms, which are rock solid. Just think about how stupid /Aggregation/ is, vs say /Composition/. Just like TTM, they have been arguing about what it is for THIRTY YEARS, and it is still not resolved. The freaks live in the Excluded Middle, that the sane reject.
Now that you have declared that the previous thread. is resolved, I do not understand why /Incomplete/ is still hanging around. Recall, there are two meanings: the original IDEF1X; and the common promoted by ERwin. The former being illegal.
4. Going over my previous post, it is plain to me that what is missing is the Predicate Lexicon. The fixed set of syntax for all the Predicates required for the implementation of a Relational database (subset of the entire FOPC possibilities). All the nuances are eliminated.
It is proprietary, published for paid customers only. Sorry.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 10 June 2021 at 19:25:02 UTC+10, Derek Ignatius Asirvadem wrote:

Clarification.

Thus any Binary Predicate can be read rCLtwo waysrCY, and only the *Verb*, not the full text, is required on the relation line, because each of the binaries is diagrammatically connected. Usually only the parent->child *Verb* is shown, and the converse is simple enough to determine [by reversal of the elements] by the reader.

Thus any Binary Predicate can be read rCLtwo waysrCY, and only the *Verb*, not the full text, is required on the relation line, because each of the Variables in the Binary Predicate is diagrammatically connected, and thus obvious.
Usually only the parent->child *Verb* is shown, and the converse is simple enough to determine [by reversal of the elements] by the reader.
Per IDEF1X syntax, the child-> parent *Verbiage* can be slash-added on a second text line on the relation line.
On Thursday, 10 June 2021 at 19:51:27 UTC+10, Derek Ignatius Asirvadem wrote:

3. In general, I would stop using OO/OOP/ORM terms when dealing with data and databases, and specifically use Relational terms when the database is Relational. Use of their terms implies their meaning, which is crippled, because OO is crippled (ambiguous; not a Standard; 420 notations; nothing clearly defined; etc). I deal with OO/OOP/ORM people all the time, and they appreciate that I do NOT use their terms, that I use Relational terms, which are rock solid. Just think about how stupid /Aggregation/ is, vs say /Composition/. Just like TTM, they have been arguing about what it is for THIRTY YEARS, and it is still not resolved. The freaks live in the Excluded Middle, that the sane reject.

I trust you understand and appreciate that the science of data and the science of process are distinct, not one science, not blurred. Thus it is a failure to take either one as primary (viewing the other as subordinate), or single (viewing the other as redundant because ours is oh so complete). The OO/OOP/ORM imbeciles do precisely that, more standing on the quicksand of Redundant, than the clever ones who have identified that the rCLmappingrCY is an undefined cloud and thus less, standing on the swamp of Primary.
The channels that transport blood are distinct from the channels that transport commands. Mixing the two, or failure to recognise that they are two, is self-cancelling. No progress can be had, despite endless arguments. Only drooling idiots, that is to say, the entire set of current academics in this field, minus you, would miss the fact that the Great Architect must have had a Good Reason for doing that. One need not mix the two, and then be surprised at the resulting paralysis or limb death.
Here at c.d.t, we are studying data science, with process science as secondary, a patron or sponsor. Not studying process science directly.
The single biggest mistake that the OO/OOP/ORM crowd make is this: they take the stand of Redundant (that their methods of perception are correct and complete, that their lens is perfect, and then they walk up to the universe and observe it.

Read /Unskilled and Unaware/ by Kr|+ger and Dunning 1999.
__ https://www.softwaregems.com.au/Documents/Reference/Unskilled%20%26%20Unaware%201999.pdf
<<<<
The Redundant stand is hysterically absurd. The Primary stand is less blind, but still crippled.
Outside the asylum, the concept of science is to observe the universe, and then to make hypotheses /from/ the observations.

Viewing the universe through the single lens of an OO Object is stupefying. Thirty years, and they still have not figured it out.
----
I wonrCOt define it here, but at least to understand the first instance, the difference: the goals and thus the principles in data science, are distinctly different to that of process science. Either one must not view the other from its own lens, but from the [defined] otherrCOs lens.
If you are at all unclear about this, please read this, with this conversation in mind. The OO/OOP/ORM world is in even worse shape now, than then:
__ https://www.softwaregems.com.au/Documents/Article/Application%20Architecture/UTOOS%20Response.pdf

Now that you have declared that the previous thread. is resolved, I do not understand why /Incomplete/ is still hanging around. Recall, there are two meanings: the original IDEF1X; and the common promoted by ERwin. The former being illegal.

Thus the former is not usable.
And the latter is meaningless, because almost by definition, every set is /Incomplete/. Thus it too is not usable.
So the classification is irrelevant, we can dismiss it.
That is what happens when an academic writes the standard, it is mush. Bridges donrCOt fall down because it is the engineers, not the lab rats, who write the Standards.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Thursday, 10 June 2021 at 19:25:02 UTC+10, Derek Ignatius Asirvadem wrote: 2.
This is an error:
__Person is described by ( SexCode, Title )

Actually no. The Predicate Lexicon is Normalised, only non-key elements are Descriptors.
This is given for the simple understanding:

__Person is described by ( NameLast, NameFirst, Initial, BirthDate, BirthCountryCode, BirthPlace, Sex, Title )

Updated.
__ https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/IDEF1X/Nicola%20IDEF1X%202.pdf

HunterLicence

The great thing about modelling rather than discussing. Particularly, the great thing about modelling visually, using IDEF1X, rather than hieroglyphics like /R={A, B, C}/, is that mistakes are exposed immediately. Likewise obvious improvements. When I added HunterLicence:
__Person is 0-or-1 HunterLicence
just does not go through the plumbing without event, it gurgles and farts. The table is renamed Hunter:
__Person is 0-or-1 Hunter
__Hunter is identified by, and dependent on, 1 Person
__Hunter is primarily identified by ( PersonNo )
__Hunter is alternately identified by ( CountryCode, StateCode, LicenceNo ) __Hunter is described by ( ExpiryDate, IsRevoked ) [1]
And ...
__Hunter is 1 Person
____Hunter is 1 Person ( <attribute>, ... )
Where <attribute> is Identifier or Descriptor.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

Certainly, I would not say that a Hunting License has a NameLast or
a BirthDate or that NameLast and BirthDate are descriptors of Hunting
License.

Why not ?

(I am assuming you are not being silly about the wording, but chasing meaning; declarative meaning.)

A Hunting licence is not a piece of paper gambolling around in the Tyrolian Alps (Fragment).
__If it were a Fragment, sure, you should not say that
__But a HuntingLicence is not a Fragment

A Hunting licence is a /Power to Act/, that is given to a Person (not animal, not thing), it does not exist
independently, it exists only in the context of Person.

Predicates, as modelled:
__HuntingLicence is dependent
__HuntingLicence is dependent on 1 Person
__HuntingLicence is identified by 1 Person
__HuntingLicence is 1 Person

Strictly, the entity is not HuntingLicence but Person-HuntingLicence.

Which is the real world, HuntingLicence is not a piece of paper (the Physical), but an option of Person (the Logical,
the Real). (Or /a/ progression of Person in the fullness of definition of Person).

So, you are saying that a 0-to-1 relationship is essentially always
"is". The key in the parent and the key in the child is the same, so the
parent and the child must describe the same "molecule", as you call it.
Am I understanding right?

So, using another verb is just for convenience. Consider this, for
instance:

https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge.pdf

The relationship reads "Problem has a Composer_2". But, that really
means "Problem is a Problem-[With]-Composer_2", doesn't it? Hence,
Composer_2 is an optional attribute of a Problem, and Problem-Composer_2
is described by a Name and a Score, just as Problem is. Right?

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-11, Nicola <nicola@nohost.org> wrote:

So, you are saying that a 0-to-1 relationship is essentially always
"is".

Sorry, that should read: any identifying 0-to-1 relationship is always
"is".

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-11, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

Whoa. You are responding to the first item that perturbed you.
Please read again, there is a bigger item coming up, or stated
otherwise, the item is bigger than you think.

No, I have read all of your replies. Then, I have replied to the point
for which I wanted a clarification. This post of yours provides the
required clarification (and more). In particular:

3. The Verb on the relation line is specific, read in both directions.
The generic Verb for any child-to-parent relation is /is/.

Think CoddrCOs 3NF & FFD only.

<FK in Child> <Verb> 1 <Parent(PK)>

The proper <Verb> is specific.
The generic <Verb> is /is/.

Finally I understand the sense in which you use those verbs.

The key in the parent and the key in the child is the same, so the

parent and the child must describe the same "molecule", as you call it.
Am I understanding right?

Not quite.

Two separate concepts. DonrCOt conflate them.

Ok, from your extensive explanation, I've also got the meaning in which
you use the term "molecule".

In an Identifying hierarchy, the child has all the keys for its
lineage; in a Non-Identifying hierarchy, the child has just the one
key for its single-level parent.

That's a prominent remark, as simple as it is. When you learn that, you
(a) understand why IDEF1X distinguishes between identifying and non-identifying, and (b) you look at the diagrams in a novel way,
immediately perceiving the (join) relationships between entities that
are far apart. At least, this is what I felt when I first understood
that. I don't think any other notation lets you say: "I can (sensibly)
join that entity on the upper-left corner with that other entity in the lower-right corner" so easily.

That Bridge problem is from my days of hard labour at TTM gulag.

Not directly relevant to the topic of this thread, and possibly not
relevant to the formulation of that problem, but... your
Solver_Not_Composer constraint only ensures that the solver is not the
main composer. But nothing in that model prevents a solver to be the
same as the second composer (i.e., SolverID may be Composer_Id_2).

I would have designed the model differently:

Person[PersonId | LastName FirstName ...]
Problem[ComposerId.PersonId ProblemNo | Name Score ...]
ProblemStakeholder[ComposerId ProblemNo StakeholderId.PersonId Role]
JointComposer[ComposerId ProblemNo JointComposer.StakeholderId]
Solver[ComposerId ProblemNo Solver.StakeholderId]

where ProblemStakeholder is an exclusive generalization of JointComposer
and Solver. Then, a simple check constraint on ProblemStakeholder
(ComposerId rea StakeholderId) plus the exclusive subtyping would prevent composers to be solvers of their own problems. An upper-bound on the
maximum number of composers or solvers for a problem could be added as
in your model.

Relationships:

Each Person invents 0-N Problems
Each Problem has 0-N ProblemStakeholders
Each ProblemStakeholder is one of JointComposer or Solver

Regards,
Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-12, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

3. The Verb on the relation line is specific, read in both directions.
The generic Verb for any child-to-parent relation is /is/.
Think CoddrCOs 3NF & FFD only.

<FK in Child> <Verb> 1 <Parent(PK)>

The proper <Verb> is specific.
The generic <Verb> is /is/.

Finally I understand the sense in which you use those verbs.

But is it not normal English ?

As a non-native speaker, there will always be nuances of the English
language I won't grasp.

I will give you one more increment.
- All tables/facts are Atoms
- Only a root Atom can be the start of a Molecule that is relevant (perceiving a Molecule in the middle of a tree is hopeless).
--- that means a Hierarch (independent, square corners)

That's clear.

--------

In an Identifying hierarchy, the child has all the keys for its
lineage; in a Non-Identifying hierarchy, the child has just the one
key for its single-level parent.

That's a prominent remark, as simple as it is. When you learn that, you
(a) understand why IDEF1X distinguishes between identifying and
non-identifying, and (b) you look at the diagrams in a novel way,
immediately perceiving the (join) relationships between entities that
are far apart. At least, this is what I felt when I first understood
that.

That is what I meant when I said /the item is bigger than you think/.
I knew a big AHA was coming. But I was shocked that you did not know
that, and surprised again, when several increments of explanation were required.

Well, it's not that I have understood that yesterday. But your comment
has reminded me of that aha! moment I indeed experienced.

By the way, I have developed a tighter Standard, IDEF1R. Therein
I have eliminated this problem of not comprehending this issue.

Perhaps, some day the world will be enlightened then? Or will your
legacy remain a privilege for few?

Regards,
Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

(Modelling instructions)

Done.
Doc updated:
____ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf
At this stage we are getting into implementation standards, such as naming. Which is perfectly fine and ordinary. But then an explanation is required. No time for that now, sorry. I trust you will see some benefit, from the usage sans explanation. Consideration: finding an object among hundreds of its type, not just the 5 to 10 objects in the subject area..
In any case, it is your model, therefore my wishes have no say.
I have used the IDEF1R symbol for ExclusiveSubtype, with the IDEF1X in the left margin for comparison. Please choose.
The rest, later.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-12, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

(Modelling instructions)

Done.

Doc updated:
____ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf

Solver_Not_Composer is implied by *_IsExclusive.

What issues do you see with a discriminator assigning more than one
value to a subtype?

primary or secondary -> Composer
solver -> Solver

Also, I don't understand this sentence:

The cumbersome <Role.PK> is replaced with <Role>.

I haven't put Role in the primary key.

I have used the IDEF1R symbol for ExclusiveSubtype, with the IDEF1X in
the left margin for comparison. Please choose.

Your symbol is fine.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-12, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

(Modelling instructions)

Done.

Doc updated:
____ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf

One more thing. Your constraints are implemented as CONSTRAINT.. CHECK
clauses. Those are checked before every insert or update. A constraint
such Solver_Max_4 would correctly prevent you from inserting a fifth
solver. But it would also prevent you to update an existing solver when
there are already four of them.

Triggers would provide a trivial solution for that, but AFAIK you don't
use them (and I agree with you on avoiding them as much as possible).
So, how do you deal with that? Delete plus insert, instead of update?

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Sunday, 13 June 2021 at 11:48:12 UTC+10, Derek Ignatius Asirvadem wrote:

In the next iteration, I will give ComposerType. (This is modelling, yes, we have many iterations). You tell me what you want.

[etc]

I forgot to mention, of course the documents is updated.

__ https://www.softwaregems.com.au/Documents/Article/Normalisation/Bridge%202.pdf

Regards
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-13, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

On Saturday, 12 June 2021 at 23:12:07 UTC+10, Nicola wrote:

On 2021-06-12, Derek Ignatius Asirvadem wrote:

What issues do you see with a discriminator assigning more than one
value to a subtype?

I think you mean /What issues do you see with a discriminator being overloaded (more than the range of subtype Discriminators) ?/

The issue here is nothing to do with Discriminators or Subtypes. It
has to do with overloading.

All good points, thanks.

So in the previous model 12.2 Jun 21, I as DBA policeman re what goes
into the db, allowed your Role, but insisted on the required
Discriminator Staketype. In the next iteration, I will give
ComposerType. (This is modelling, yes, we have many iterations). You
tell me what you want.

The latest revision, with ComposerType, is fine.

Also, I don't understand this sentence:

The cumbersome <Role.PK> is replaced with <Role>.

I haven't put Role in the primary key.

Not the column name Role, but the /RM/ concept of Role.

Ah! Got confused by the names. Sure, I have used <Role.PK> for
extra-clarity of the text-only description.

On Saturday, 12 June 2021 at 18:22:59 UTC+10, Nicola wrote:

Then, Solver_Not_Composer, Solver_Not_JointComposer, and
JointComposer_Not_Composer all reduce to a single check in
StakeHolder.

Implementation standards again.
Mine require each CHECK constraint to do just one logical thing,
generally that means one <LogicalCondition> per constraint.

Ok, makes sense. Again, good points.

While I am here,let me point out something about naming. For meaning
and brevity, the constraint name is:
__ Secondary_Max_1
The table prefix is excluded in the model at the table entry, but
demanded in the physical (otherwise you will never find the constraint
in the list of constraints), and is so listed at the expansion at the
bottom:
__ Composer_Secondary_Max_1
Along with the full CHECK condition given in pseudo-sql (my notation,
which you have seen before):
__ CHECK Composer[ @Primary ] EXISTS
__ AND
__ CHECK Composer[ @Secondary ] NOT EXISTS

Ok. The notation is pretty much self-explaining.

See, even typing that exposes that it is incorrect. Ah, the wonder of
the iterations in data modelling.
__ CHECK Composer.IsPrimary[ @ProblemName ] EXISTS
__ AND
__ CHECK ( Composer.IsPrimary[ @ProblemName ] = 0 ) NOT EXISTS

I think that you still have a typo there. The second clause should read:

[AND] CHECK ( Composer.~~~IsSecondary~~~[ @ProblemName ] = 0 ) NOT EXISTS

For a bit of a write-up on naming, see this post. SO actively
suppresses the truth, same as the /RM/ saboteurs:
__ https://stackoverflow.com/a/4703155/484814

I know that post. I found it while searching for naming standards some
time ago. Good advice, generally speaking, although I do not follow all
of it. For instance, I find the recommendation for large subject areas
(use table prefixes) rather odd, when logical separation is precisely
the purpose of schemas (CREATE SCHEMA). The advice is good for platforms
not supporting schemas, such as MySQL, though.

On Saturday, 12 June 2021 at 23:45:52 UTC+10, Nicola wrote:

On 2021-06-12, Derek Ignatius Asirvadem wrote:

One more thing. Your constraints are implemented as CONSTRAINT.. CHECK
clauses. Those are checked before every insert or update. A constraint
such Solver_Max_4 would correctly prevent you from inserting a fifth
solver. But it would also prevent you to update an existing solver when
there are already four of them.

Triggers would provide a trivial solution for that, but AFAIK you don't
use them (and I agree with you on avoiding them as much as possible).

Absolutely.
I have never written a trigger in my life.
I have examined and replaced over 2,000 triggers in customer databases
with proper DDL and Transactions.

So, how do you deal with that? Delete plus insert, instead of update?

That is a question that should be answered fully. Considerations.

Ok, understood. Nothing to add here.

4. Not on my platform.
The CHECK constraint is fired for UPDATE only if a column named in the constraint is affected. __ In this case, no columns are named, meaning
it is not CHECKed on UPDATE __ And I can force the constraint to be
CHECKed by naming a column (There is a huge value in commercial SQL platforms, that the freeware crowd cannot even dream of.)

Ok, I didn't know that.

Example from MS/SQL, which is the same as mine in this issue: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdb87a2e-41ce-4a97-8a14-3f218eb00721/function-check-constraint-ignored?forum=transactsql

Funny example. The OP's constraint wouldn't work even if the constraints
were triggered, because their condition is checked *before* the update
is performed. So, when changing IsActive from true to false in the
parent, or when changing MId to point to a record in M with IsActive set
to false, the corresponding constraint must evaluate to true (otherwise
the data would not be consistent to begin with), so such updates are
allowed.

I do not see how you could prevent them with CONSTRAINT.. CHECK.
Ironically (since you posted that), the first reply in that thread
recommends: "using scalar UDFs that read the database in a CHECK
constraint is not a good practice. Triggers are simpler and more
useful". Indeed, that example would be solved easily with triggers.

Alternatively, you'd have to revoke writes and use stored procedures to
perform semantically meaningful operations. Yes, I know you do that.

I do not see other ways.

5. For others.
In general, yes, DELETE+INSERT.
Note that one should be used to that because UPDATE any Key component
is not permitted, it must be DELETE+INSERT, in a Transaction of
course, that moves the entire hierarchy belonging to the Key.

That is because in your experience, cascading updates (UPDATE ...
CASCADE), are inefficient, right? I'd like to do some benchmarks. So,
how do you code them?

start transaction;
update Grand1_Grand2...GrandN_Child;
update Grand1_Grand2...Grand(N-1)_Child
...
update Child;
update Parent;
commit;

?

I forgot to mention, of course the documents is updated.

Looks good to me (except for the above mentioned typo).

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-13, Nicola <nicola@nohost.org> wrote:

Example from MS/SQL, which is the same as mine in this issue:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdb87a2e-41ce-4a97-8a14-3f218eb00721/function-check-constraint-ignored?forum=transactsql

Funny example. The OP's constraint wouldn't work even if the constraints
were triggered,
[...]
I do not see how you could prevent them with CONSTRAINT.. CHECK.
[...]
I do not see other ways.

No no, of course it can be done with constraints. I got confused by the
fact that the OP defines one function to implement different
constraints.

A good example of issues arising from conflating different conditions.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2021-06-14, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

I think that you still have a typo there. The second clause should read:

[AND] CHECK ( Composer.~~~IsSecondary~~~[ @ProblemName ] = 0 ) NOT EXISTS

[...]
So this:
__ CHECK ( Composer.IsPrimary[ @ProblemName ] = 0 ) NOT EXISTS
checks that a Secondary does not exist.

I stand corrected.

Example from MS/SQL, which is the same as mine in this issue:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdb87a2e-41ce-4a97-8a14-3f218eb00721/function-check-constraint-ignored?forum=transactsql

Ironically (since you posted that), the first reply in that thread
recommends: "using scalar UDFs that read the database in a CHECK
constraint is not a good practice. Triggers are simpler and more
useful". Indeed, that example would be solved easily with triggers.

1. Triggers are simply stupid, maintenance is horrendous. Total
unnecessary in the real world of Open Architecture,
[...]
Using Functions that are called by CHECK Constraints is pedestrian,
the world has been doing it since 2007. CHECK can only check the
inserted row. CHECK+Function can check other rows in the table, or any
other table.

For platforms where that is true, I totally agree.

I can code that way in PostgreSQL, and it is an elegant approach. It
even appears to work in simple tests, which is nice because I can try
that approach. Unfortunately, it is not really supported (it fails under concurrent loadsrCohave fun with this comment), so triggers are the only alternative in most cases. But you get what you pay for, right?

On the other hand, a *disciplined* use of triggers, restricted to such constraints, is fine. It achieves the same results as declarative
constraints, with some added flexibility and efficiency, because you can specify exactly which operations on which columns under which conditions
should fire the trigger. Error reporting is just as expressive, or more
(custom messages). The trigger shows up just as a constraint associated
to a table when you inspect the table metadata. Just slightly more
verbose to write.

Same as using prefixes because you have no schemas. Not ideal, but still workable.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Monday, 14 June 2021 at 17:03:04 UTC+10, Nicola wrote:

On 2021-06-14, Derek Ignatius Asirvadem wrote:

1. Triggers are simply stupid, maintenance is horrendous. Total unnecessary in the real world of Open Architecture,
[...]
Using Functions that are called by CHECK Constraints is pedestrian,
the world has been doing it since 2007. CHECK can only check the
inserted row. CHECK+Function can check other rows in the table, or any other table.

For platforms where that is true, I totally agree.

I can code that way in PostgreSQL, and it is an elegant approach. It
even appears to work in simple tests, which is nice because I can try
that approach. Unfortunately, it is not really supported (it fails
under concurrent loads

So you canrCOt code that way in PusGres. DonrCOt contradict yourself. DonrCOt kid yourself, it is a single-user flatporn.

rCohave fun with this comment), so triggers are the only
alternative in most cases. But you get what you pay for, right?

As long as you donrCOt lie to yourself and say that that is SQL, fine with me. And is something doesnrCOt work, donrCOt lie to yourself and say that SQL is broken, say that your o.o.s pretend sql canrCOt do that.

On the other hand, a *disciplined* use of triggers, restricted to such constraints, is fine. It achieves the same results as declarative constraints, with some added flexibility and efficiency, because you can specify exactly which operations on which columns under which conditions should fire the trigger. Error reporting is just as expressive, or more (custom messages). The trigger shows up just as a constraint associated
to a table when you inspect the table metadata. Just slightly more
verbose to write.

Just donrCOt say that that is SQL.
Have fun with the 100% rewrite, when you move to an SQL platform.

Same as using prefixes because you have no schemas. Not ideal, but still workable.

Having no schemas is like having no herpes. Tell me, give me one good reason why I should duplicate some subset of my 100% Normalised DDL, which achievement was not small. Think: Data Modeller and DBA of a financial database of 500 tables in four physical databases. Why should I add this additional layer of abstraction, that I did not need for twenty years before its introduction, to my fully Logical command and control of the database.
And it is not rCLthe same asrCY. That is comparing something that was produced by design, with something that is an accident, patched up to make it water-tight: such are not comparable. It doesnrCOt matter that the result looks the same, they are not. The child born out of wedlock is not the same as the child of a sanctified marriage: they look and smell the same, but internally they are quite different. One is the result of Design, it is the effect of a cause, the other is not-caused, an accident of some other intent.
Like the evolutionist fish, that magically developed lungs for no reason, that mystically became advantageous when it mysteriously crawled onto land for no reason, and instantly manifested all the muscles and rib structures required to act as bellows. By accident. I donrCOt have enough blind faith to believe in such stories.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2