From Newsgroup: comp.databases.theory

On 2020-01-09, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

In Node(NodeNo, ParentNo, Name)? I am assuming that you recognize the
root of the hierarchy as a tuple of the type (n,n,''), for some integer
n. Please clarify if that is not the case.

No. As a rule, I have no fudges. Ever.

There is just one root for the entire Node tree. The RM does not
permit, and we do not have circular references and therefore we do not
need "deferred constraint checking". (Note that the TTM Gulag teaches
you to place a sabotaging circular reference practically everywhere.
To justify their "need" for "deferred constraint checking".)

During the database build, which is a rather large script that is
sequenced, right after the
CREATE TABLE Node
command, before any of the
ALTER TABLE Node ADD CONSTRAINT ...
do:
INSERT Node VALUES ( 0, 0, CHAR(0) )

So, you do have a row of the form (n,n,...) (with n=0) to denote the one
root of the tree.

now proceed with the
ALTER TABLE Node ADD CONSTRAINT ...
one of which is:
ALTER TABLE Node
ADD CONSTRAINT Node_NE_Parent_ck CHECK (
NodeNo <> ParentNodeNo
)

Now, wait. Does Sybase really accept that? Regardless of the answer,
that *is* anti-logical:

- Declared constraint in table definition: NodeNo <> ParentNodeNo
- Current instance of the table:

Nodeno ParentNodeNo ...
0 0 ...

Tell me what you want, that CONSTRAINT CHECKs are enforced at update
time, that the constraint did not exist when the row was inserted, etc.
but what you show is a dangerous behavior. You cannot trust that
a declared constraint is indeed satisfied by your instances any more.

The system must reject the addition of a constraint that the current
instance violates, period.

Nicola
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On Friday, 10 January 2020 01:57:46 UTC+11, Derek Ignatius Asirvadem wrote:

Actually, the problem, the difficulty you guys have in accepting the logical and painless way to perform ordinary database maintenance, is menatl. You are so addicted to tearing your hair out, to having a problem that cannot be solved, etc, all due to the "deferred constraint checking" mindset.
First, you do the task the wrong way. Second, you have yourself in a quandary, a knot that cannot be undone. Ooooh the excitement. Ok, fine. But WE don't have to have the same series of errors, or the same consequence.
(
This kind of story happens to me all the time. A couple of years ago I was at a short assignment at a large bank in New Zealand. I got along well with the senior users, and my boss, the IT executive asked me to do any small tasks the users asked, no need to get approval from him. No worries. In-between server and database rebuild tasks, an Auditor asked me if I could get a certain report out of the server. I said, sure, give me 15 minutes. Two hours later, the boss came around, took me out to lunch and gave me an earful. Turned out the IT guys had said that that report was not possible. For five years. He told me not to do any more jobs for the users. When we got back from lunch, all the senior users were in the boardroom with all the IT guys. There was gnashing of teeth, wailing like widows. None of the IT guys spoke to me after that.
)
You guys have this quandary, this knot from hell inside your non-server from hell, on every single instance of "deferred constraint checking".
You guys do not know any other way. And when you find out, it makes you livid, at the hundreds of hours you have wasted with that quandary, so many many times. But you focus that anger at the messenger, not at the academics who lied to you, that are the real cause of your quandary, both the technical one and the mental one.
Cheers
Derek
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.theory

On 2020-01-09, Derek Ignatius Asirvadem <derek.asirvadem@gmail.com> wrote:

now proceed with the
ALTER TABLE Node ADD CONSTRAINT ...
one of which is:
ALTER TABLE Node
ADD CONSTRAINT Node_NE_Parent_ck CHECK (
NodeNo <> ParentNodeNo
)

Now, wait. Does Sybase really accept that? Regardless of the answer,
that *is* anti-logical:

- Declared constraint in table definition: NodeNo <> ParentNodeNo
- Current instance of the table:

Nodeno ParentNodeNo ...
0 0 ...
[...]
The system must reject the addition of a constraint that the current
instance violates, period.

Instead of telling me what the genuine SQL platforms SHOULD do, from
your perspective, based on experience with an anti-SQL piece of dog's
meat, why don't you find out (a) what SQL requires, and (b) why we
might want the operation that the SQL platforms do.

ISO SQL:2011 (the draft I have at hand), -o4.18.3.4:

"A table check constraint is satisfied if and only if the specified
<search condition> evaluates to True or Unknown *for every row* of the
table to which it applies." [emphasis mine]

FYI, you can do what you describe in PostgreSQL as well, but better:

alter table Node
add constraint Node_NE_Parent_ck
check (NodeNo <> ParentNodeNo) NOT VALID;

The default is sane (if you add a constraint, usually you want every
instance to conform to it), but you may skip validation with NOT VALID.
No performance penalty, as you ask: the constraint will be enforced only
for subsequent inserts or updates.

Second, the constraint is marked as such in the table definition, e.g.:

[table definition here]
Check constraints:
"Node_NE_Parent_ck" CHECK (NodeNo <> ParentNodeNo) NOT VALID

so you know that it is not necessarily satisfied by each row.

Third, if you want you may validate it at any subsequent time with:

alter table Node validate constraint Node_NE_Parent_ck;

In your example, that would get you an error because of the first
insertion:

ERROR: check constraint "Node_NE_Parent_ck" is violated by some row

and the constraint will remain NOT VALID.

Granted, there are use cases when this feature comes in handy, like
adding a constraint to a huge table. But the idea is that you'd skip the constraint because you *know* that it's valid for the existing data.
There are exceptions, of course ("hey, the codes from now on will be
only numeric; but the alphanumeric codes are still in use and should not
be changed"). But if that feature can be avoided, IMO it's better to
avoid it.

In your example, this would be a cleaner solution:

alter table Node
add constraint Node_NE_Parent_ck
check (NodeNo <> ParentNodeNo or NodeNo = 0);

This could be added before inserting any data and it *declares*
explicitly that we don't care about node 0.

Nicola

--- Synchronet 3.21d-Linux NewsLink 1.2