Repository: ariga/atlas
Pull request #455 (2022-01-20 21:56:40)

sql/mysql: CHECK constraints are ENFORCED by default


Patch:
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index b39b77ee..ac794801 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -56,7 +56,7 @@ func (d *diff) TableAttrDiff(from, to *schema.Table) ([]schema.Change, error) {
 	// using "MODIFY COLUMN".
 	var checks []schema.Change
 	for _, c := range sqlx.CheckDiff(from, to, func(c1, c2 *schema.Check) bool {
-		return c1.Expr != c2.Expr || sqlx.Has(c1.Attrs, &Enforced{}) != sqlx.Has(c2.Attrs, &Enforced{})
+		return c1.Expr != c2.Expr || enforced(c1.Attrs) != enforced(c2.Attrs)
 	}) {
 		drop, ok := c.(*schema.DropCheck)
 		if !ok || !strings.HasPrefix(drop.C.Expr, "json_valid") {
@@ -263,6 +263,15 @@ func indexType(attr []schema.Attr) *IndexType {
 	return t
 }
 
+// enforced returns the ENFORCED attribute for the CHECK
+// constraint. A CHECK is ENFORCED if not state otherwise.
+func enforced(attr []schema.Attr) bool {
+	if e := (Enforced{}); sqlx.Has(attr, &e) {
+		return e.V
+	}
+	return true
+}
+
 // noChange describes a zero change.
 var noChange struct{ schema.Change }
 
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 07de9c1a..f9996b78 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -398,14 +398,10 @@ func (i *inspect) checks(ctx context.Context, t *schema.Table) error {
 			Name: name.String,
 			Expr: unescape(clause.String),
 		}
-		if enforced.String != "NO" {
-			check.Attrs = append(check.Attrs, &Enforced{})
-		}
-		t.Attrs = append(t.Attrs, check)
-		// In MariaDB, JSON is an alias to LONGTEXT. For versions >= 10.4.3, the CHARSET and COLLATE set to utf8mb4
-		// and a CHECK constraint is automatically created for the column as well (i.e. JSON_VALID(`<C>`)). However,
-		// we expect tools like Atlas and Ent to manually add this CHECK for older versions of MariaDB.
 		if i.mariadb() {
+			// In MariaDB, JSON is an alias to LONGTEXT. For versions >= 10.4.3, the CHARSET and COLLATE set to utf8mb4
+			// and a CHECK constraint is automatically created for the column as well (i.e. JSON_VALID(`<C>`)). However,
+			// we expect tools like Atlas and Ent to manually add this CHECK for older versions of MariaDB.
 			c, ok := t.Column(check.Name)
 			if ok && c.Type.Raw == TypeLongText && check.Expr == fmt.Sprintf("json_valid(`%s`)", c.Name) {
 				c.Type.Raw = TypeJSON
@@ -414,8 +410,13 @@ func (i *inspect) checks(ctx context.Context, t *schema.Table) error {
 				// as they are valid only for character types.
 				c.UnsetCharset().UnsetCollation()
 			}
+		} else if enforced.String == "NO" {
+			// The ENFORCED attribute is not supported by MariaDB.
+			// Also, skip adding it in case the CHECK is ENFORCED,
+			// as the default is ENFORCED if not state otherwise.
+			check.Attrs = append(check.Attrs, &Enforced{V: false})
 		}
-
+		t.Attrs = append(t.Attrs, check)
 	}
 	return rows.Err()
 }
@@ -725,9 +726,9 @@ type (
 	}
 
 	// Enforced attribute defines the ENFORCED flag for CHECK constraint.
-	// Similar to AUTO_INCREMENT, checks with this attributes are enforced.
 	Enforced struct {
 		schema.Attr
+		V bool // V indicates if the CHECK is enforced or not.
 	}
 
 	// The DisplayWidth represents a display width of an integer type.
diff --git a/sql/mysql/sqlspec.go b/sql/mysql/sqlspec.go
index 14edb225..eef8db23 100644
--- a/sql/mysql/sqlspec.go
+++ b/sql/mysql/sqlspec.go
@@ -99,9 +99,7 @@ func convertCheck(spec *sqlspec.Check) (*schema.Check, error) {
 		if err != nil {
 			return nil, err
 		}
-		if b {
-			c.AddAttrs(&Enforced{})
-		}
+		c.AddAttrs(&Enforced{V: b})
 	}
 	return c, nil
 }


Test patch:
diff --git a/internal/integration/mysql_test.go b/internal/integration/mysql_test.go
index eedcc580..98d5e288 100644
--- a/internal/integration/mysql_test.go
+++ b/internal/integration/mysql_test.go
@@ -921,7 +921,7 @@ create table atlas_types_sanity
 						return []schema.Attr{
 							&schema.Charset{V: "latin1"},
 							&schema.Collation{V: "latin1_swedish_ci"},
-							&schema.Check{Name: "tJSON", Expr: "json_valid(`tJSON`)", Attrs: []schema.Attr{&mysql.Enforced{}}},
+							&schema.Check{Name: "tJSON", Expr: "json_valid(`tJSON`)"},
 						}
 					}
 					return []schema.Attr{
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index cb2517f3..8195cfd6 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -606,7 +606,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "c1", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 				}
 				require.EqualValues(columns, t.Columns)
-				require.EqualValues([]schema.Attr{&CreateStmt{S: "CREATE TABLE users()"}, &schema.Check{Name: "users_chk_1", Expr: "(`c6` <>_latin1\\'foo\\'s\\')", Attrs: []schema.Attr{&Enforced{}}}}, t.Attrs)
+				require.EqualValues([]schema.Attr{&CreateStmt{S: "CREATE TABLE users()"}, &schema.Check{Name: "users_chk_1", Expr: "(`c6` <>_latin1\\'foo\\'s\\')"}}, t.Attrs)
 			},
 		},
 	}
diff --git a/sql/mysql/sqlspec_test.go b/sql/mysql/sqlspec_test.go
index 002e471e..7a6af0ca 100644
--- a/sql/mysql/sqlspec_test.go
+++ b/sql/mysql/sqlspec_test.go
@@ -57,6 +57,10 @@ table "table" {
 		expr     = "price1 <> price2"
 		enforced = true
 	}
+	check {
+		expr     = "price2 <> price1"
+		enforced = false
+	}
 	comment = "table comment"
 }
 
@@ -133,7 +137,11 @@ table "accounts" {
 				},
 				&schema.Check{
 					Expr:  "price1 <> price2",
-					Attrs: []schema.Attr{&Enforced{}},
+					Attrs: []schema.Attr{&Enforced{V: true}},
+				},
+				&schema.Check{
+					Expr:  "price2 <> price1",
+					Attrs: []schema.Attr{&Enforced{V: false}},
 				},
 				&schema.Comment{Text: "table comment"},
 			},