Hi Ariel, Your suggestions make sense, and I’ll be updating the CEP with the details. Basically: - We have an optional name for the constraints. If the name is not provided, a random name is generated for a constraint: CREATE TABLE keyspace.table ( p1 int, p2 int, ..., CONSTRAINT [name] CHECK p1 != p2 );
- Alter and Drop constraints are as follows ALTER CONSTRAINT [name] CHECK new_condition DROP CONSTRAINT [name] - Describe table returns the list of constraints for a table. - The condition of the CONSTRAINT (after the CHECK keyword) can be surrounded by optional parentheses to keep consistency with other databases syntax. I will update the CEP with those details. To Dinesh’s point, I agree that a NOT NULL constraint will be really useful. I can add it to the list on the CEP Regards, Bernardo > On Jun 25, 2024, at 9:22 AM, Ariel Weisberg <ar...@weisberg.ws> wrote: > > Hi, > > I am also +1 on Doug's distinction between things that can be managed by > operators and things that can be managed by applications. > > Some things to note about the syntax is that there are parens around the > condition in SQL. In your example there are multiple anonymous constraints on > the same column, how are anonymous constraints handled? Does the database > automatically generate a named constraint for them so they can be referenced > later? Do we allow multiple constraints on the same column and AND them > together? > > Ariel > > > > On Mon, Jun 24, 2024, at 6:43 PM, Bernardo Botella wrote: >> Hi Ariel and Jon, >> >> Let me address your question first. Yes, AND is supported in the proposal. >> Below you can find some examples of different constraints applied to the >> same column. >> >> As per the LENGTH name instead of sizeOf as in the proposal, I am also not >> opposed to it if it is more consistent with terminology in the databases >> universe. >> >> So, to recap, there seems to be general agreement on the usefulness of the >> Constraints Framework. >> Now, from the feedback that has arrived after the voting has been called, I >> see there are three different proposals for syntax: >> >> 1.- >> The syntax currently described in the CEP. Example: >> CREATE TYPE keyspace.cidr_address_ipv4 ( >> ip_adress inet, >> subnet_mask int, >> CONSTRAINT subnet_mask > 0, >> CONSTRAINT subnet_mask < 32 >> ) >> >> 2.- >> As Jon suggested, leaving this definitions to more specific Guardrails at >> table level. Example, something like: >> column_min_int_value_size_threshold_keyspace_address_ipv4_ip_adress = 0 >> column_max_int_value_size_threshold_keyspace_address_ipv4_ip_adress = 32 >> >> 3.- >> As Ariel suggested, having the CHECK keyword added to align consistency with >> SQL. Example: >> CREATE TYPE keyspace.cidr_address_ipv4 ( >> ip_adress inet, >> subnet_mask int, >> CONSTRAINT CHECK subnet_mask > 0, >> CONSTRAINT CHECK subnet_mask < 32 >> ) >> >> For the guardrails vs cql syntax, I think that keeping the conceptual >> separation that has been explored in this thread, and perfectly recapped by >> Doug, is closer to what we are trying to achieve with this framework. In my >> opinion, having them in the CQL schema definition provides those application >> level constraints that Doug mentions in an more accesible way than having to >> configure such specific guardrais. >> >> For the addition of the CHECK keyword, I'm definitely not opposed to it if >> it helps Cassandra users coming from other databases understand concepts >> that were already familiar to them. >> >> I hope this helps move the conversation forward, >> Bernardo >> >> >> >>> On Jun 24, 2024, at 12:17 PM, Ariel Weisberg <ar...@weisberg.ws> wrote: >>> >>> Hi, >>> >>> I see a vote for this has been called. I should have provided more prompt >>> feedback sooner. >>> >>> I am a strong +1 on adding column level constraints being a good thing to >>> add. I'm not too concerned about row/partition/table level constraints, but >>> I would like to change the syntax before I would be +1 on this CEP. >>> >>> It would be good to align the syntax as closely as possible to our existing >>> syntax, and if not that then MySQL/Postgres. For example it looks like we >>> don't have a string length function so maybe add `LENGTH` (consistent with >>> MySQL/Postgres) to also use with column level constraints. >>> >>> It looks like there are generally two forms of constraint syntax, one is >>> expressed as part of the column definition, and the other is a named or >>> anonymous constraint on the table. >>> https://www.w3schools.com/sql/sql_check.asp >>> >>> Can we align with having these column level ones as `CHECK` constraints >>> like in SQL, and `CONSTRAINT [constraint_name] CHECK` would be used if >>> creating a named or multi-column constraint? >>> >>> Will column level check constraints support `AND` so that you can specify >>> multiple constraints on the column? I am not sure if that is supported in >>> other databases, but it would be good to align on that as well. >>> >>> RE some implementation things to keep in mind: >>> >>> If TCM is in use and the constraints are defined in the schema data >>> structure this should work fine with Accord because all coordinators >>> (regular, recovery) will deterministically agree on the constraints being >>> enforced BUT... this also has to map to how/when constraints are enforced. >>> >>> Both Accord and Paxos work best when the constraints are enforced when the >>> final mutation to be applied is created and not later when it is being >>> applied to the CFS. This also reduces duplication of enforcement checking >>> work to just the coordinator for the write. >>> >>> Ariel >>> >>> On Fri, May 31, 2024, at 5:23 PM, Bernardo Botella wrote: >>>> Hello everyone, >>>> >>>> I am proposing this CEP: >>>> CEP-42: Constraints Framework - CASSANDRA - Apache Software Foundation >>>> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-42%3A+Constraints+Framework> >>>> cwiki.apache.org >>>> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-42%3A+Constraints+Framework> >>>> <favicon.ico> >>>> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-42%3A+Constraints+Framework> >>>> >>>> And I’m looking for feedback from the community. >>>> >>>> Thanks a lot! >>>> Bernardo
