Hi. These was a topic we discussed during the ML thread: https://lists.apache.org/thread/xc2phmxgsc7t3y9b23079vbflrhyyywj
Here was one of my answers on that: https://lists.apache.org/thread/76olqf6225noygxcclsrs56ngnlmcvxv It was also specified in the CEP (https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-42%3A+Constraints+Framework#CEP42:ConstraintsFramework-Constraintexecutionatwritetime): "Note: This constraints are only enforced at write time. So, an ALTER CONSTRAINT with more restrictive constraints shouldn’t affect preexisting data.” Long story short, constraints are only checked at write time. If a constraint is added to a table with preexisting offending data, that data stays untouched. I hope this helps, Bernardo > On Feb 10, 2025, at 7:00 AM, Benedict <bened...@apache.org> wrote: > > This is counterintuitive to me. The constraint should be applied to the > table, not to the update. NOT NULL should imply a value is always specified. > > How are you handling this for tables that already exist? Can we alter table > to add constraints, and if so what are the semantics? > >> On 10 Feb 2025, at 14:50, Bernardo Botella <conta...@bernardobotella.com> >> wrote: >> >> Hi everyone, >> >> Stefan Miklosovic and I have been working on a NOT_NULL >> (https://github.com/apache/cassandra/pull/3867) constraint to be added to >> the constraints tool belt, and a really interesting conversation came up. >> >> First, as a problem statement, let's consider this: >> >> ----------------------------------------- >> CREATE TABLE ks.tb2 ( >> id int, >> cl1 int, >> cl2 int, >> val text CHECK NOT_NULL(val), >> PRIMARY KEY (id, cl1, cl2) >> ) >> >> cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2, val) VALUES ( 1, 2, 3, >> null); >> InvalidRequest: Error from server: code=2200 [Invalid query] message="Column >> value does not satisfy value constraint for column 'val' as it is null." >> >> cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2, val) VALUES ( 1, 2, 3, >> “text"); >> cassandra@cqlsh> select * from ks.tb2; >> >> id | cl1 | cl2 | val >> ----+-----+-----+------ >> 1 | 2 | 3 | text >> >> (1 rows) >> cassandra@cqlsh> INSERT INTO ks.tb2 (id, cl1, cl2) VALUES ( 1, 2, 4); >> cassandra@cqlsh> select * from ks.tb2; >> >> id | cl1 | cl2 | val >> ----+-----+-----+------ >> 1 | 2 | 3 | text >> 1 | 2 | 4 | null >> >> ----------------------------------------- >> >> As you see, we have a hole in which a 'null' value is getting written on >> column val even if we have a NOT_NULL on that particular column whenever the >> column is NOT specified on the write. That raises the question on how this >> particular constraint should behave. >> >> If we consider the other constraints (scalar constraint and length >> constraint so far), this particular behavior is fine. But, if the constraint >> is NOT_NULL, then it becomes a little bit trickier. >> >> The conclusions we have reached is that the meaning of constraints should be >> interpreted like: I check whatever you give me as part of the write, >> ignoring everything else. Let me elaborate: >> If we decide to treat this particular NOT_NULL constraint differently, and >> check if the value for that column is present in the insert statement, we >> then open a different can of worms. What happens if the row already exists >> with a valid value, and that insert statement is only trying to do an update >> to a different column in the row? If that was the case, we would be forcing >> the user to specify the 'val' column value for every update, even if it is >> not needed. >> >> Mainly for this reason, we think it is better to treat this NOT_NULL >> constraint just like the other constraints, and execute it ONLY on the >> values that are present on the insert statement. >> >> The main con is that it may lead to a little bit of confussion (as in, why I >> just added a null value to the table even if I have a NOT_NULL constraint?). >> We have thought on aliviating this particular confusion by: >> - Extensive documentation. Let's be upfront on what this constraint does and >> does not. >> (https://github.com/apache/cassandra/blob/ed58c404e8c880b69584e71a3690d3d9f73ef9fa/doc/modules/cassandra/pages/developing/cql/constraints.adoc#not_null-constraint) >> - Adding, as part of this patch, yet another constraint (STRICTLY_NOT_NULL), >> that checks for the actual column value to be present in the insert >> statement.. >> >> If you've made it until here, that means you are really interested in >> constraints. Thanks! The question for you is, would you have any concern >> with this approach? >> >> Thanks, >> Bernardo
