Repository: genjidb/genji
Pull request #436 (2021-11-09 17:06:28)

Add support for CHECK
This adds support for the CHECK table constraint


Patch:
diff --git a/internal/database/catalog.go b/internal/database/catalog.go
index 3a144784..4ca6dbff 100644
--- a/internal/database/catalog.go
+++ b/internal/database/catalog.go
@@ -338,7 +338,7 @@ func (c *Catalog) dropIndex(tx *Transaction, name string) error {
 }
 
 // AddFieldConstraint adds a field constraint to a table.
-func (c *Catalog) AddFieldConstraint(tx *Transaction, tableName string, fc FieldConstraint) error {
+func (c *Catalog) AddFieldConstraint(tx *Transaction, tableName string, fc *FieldConstraint, tcs TableConstraints) error {
 	r, err := c.Cache.Get(RelationTableType, tableName)
 	if err != nil {
 		return err
@@ -346,7 +346,14 @@ func (c *Catalog) AddFieldConstraint(tx *Transaction, tableName string, fc Field
 	ti := r.(*TableInfo)
 
 	clone := ti.Clone()
-	err = clone.FieldConstraints.Add(&fc)
+	if fc != nil {
+		err = clone.FieldConstraints.Add(fc)
+		if err != nil {
+			return err
+		}
+	}
+
+	err = clone.TableConstraints.Merge(tcs)
 	if err != nil {
 		return err
 	}
@@ -761,6 +768,16 @@ func newCatalogStore() *CatalogStore {
 		info: &TableInfo{
 			TableName: TableName,
 			StoreName: []byte(TableName),
+			TableConstraints: []*TableConstraint{
+				{
+					PrimaryKey: true,
+					Path: document.Path{
+						document.PathFragment{
+							FieldName: "name",
+						},
+					},
+				},
+			},
 			FieldConstraints: []*FieldConstraint{
 				{
 					Path: document.Path{
@@ -768,8 +785,7 @@ func newCatalogStore() *CatalogStore {
 							FieldName: "name",
 						},
 					},
-					Type:         types.TextValue,
-					IsPrimaryKey: true,
+					Type: types.TextValue,
 				},
 				{
 					Path: document.Path{
diff --git a/internal/database/conflict.go b/internal/database/conflict.go
index 92b7ff3a..67c7cf2f 100644
--- a/internal/database/conflict.go
+++ b/internal/database/conflict.go
@@ -26,6 +26,6 @@ func OnInsertConflictDoReplace(t *Table, key []byte, d types.Document, err error
 	return documentWithKey{
 		Document: d,
 		key:      key,
-		pk:       t.Info.FieldConstraints.GetPrimaryKey(),
+		pk:       t.Info.GetPrimaryKey(),
 	}, nil
 }
diff --git a/internal/database/constraint.go b/internal/database/constraint.go
index 37258bf0..615209e3 100644
--- a/internal/database/constraint.go
+++ b/internal/database/constraint.go
@@ -1,6 +1,7 @@
 package database
 
 import (
+	"strconv"
 	"strings"
 
 	"github.com/genjidb/genji/document"
@@ -22,11 +23,8 @@ func (c *ConstraintViolationError) Error() string {
 type FieldConstraint struct {
 	Path         document.Path
 	Type         types.ValueType
-	IsPrimaryKey bool
 	IsNotNull    bool
-	IsUnique     bool
 	DefaultValue TableExpression
-	Identity     *FieldConstraintIdentity
 	IsInferred   bool
 	InferredBy   []document.Path
 }
@@ -42,10 +40,6 @@ func (f *FieldConstraint) IsEqual(other *FieldConstraint) bool {
 		return false
 	}
 
-	if f.IsPrimaryKey != other.IsPrimaryKey {
-		return false
-	}
-
 	if f.IsNotNull != other.IsNotNull {
 		return false
 	}
@@ -60,13 +54,13 @@ func (f *FieldConstraint) IsEqual(other *FieldConstraint) bool {
 		}
 	}
 
-	if !f.Identity.IsEqual(other.Identity) {
-		return false
-	}
-
 	return true
 }
 
+func (f *FieldConstraint) IsEmpty() bool {
+	return f.Type.IsAny() && !f.IsNotNull && f.DefaultValue == nil
+}
+
 func (f *FieldConstraint) String() string {
 	var s strings.Builder
 
@@ -78,14 +72,6 @@ func (f *FieldConstraint) String() string {
 		s.WriteString(" NOT NULL")
 	}
 
-	if f.IsPrimaryKey {
-		s.WriteString(" PRIMARY KEY")
-	}
-
-	if f.IsUnique {
-		s.WriteString(" UNIQUE")
-	}
-
 	if f.HasDefaultValue() {
 		s.WriteString(" DEFAULT ")
 		s.WriteString(f.DefaultValue.String())
@@ -136,18 +122,6 @@ func (f FieldConstraints) Get(path document.Path) *FieldConstraint {
 	return nil
 }
 
-// GetPrimaryKey returns the field constraint of the primary key.
-// Returns nil if there is no primary key.
-func (f FieldConstraints) GetPrimaryKey() *FieldConstraint {
-	for _, fc := range f {
-		if fc.IsPrimaryKey {
-			return fc
-		}
-	}
-
-	return nil
-}
-
 // Infer additional constraints based on user defined ones.
 // For example, given the following table:
 //   CREATE TABLE foo (
@@ -231,7 +205,7 @@ func (f *FieldConstraints) Add(newFc *FieldConstraint) error {
 			// the inferred one may has less constraints than the user-defined one
 			inferredFc.DefaultValue = nonInferredFc.DefaultValue
 			inferredFc.IsNotNull = nonInferredFc.IsNotNull
-			inferredFc.IsPrimaryKey = nonInferredFc.IsPrimaryKey
+			// inferredFc.IsPrimaryKey = nonInferredFc.IsPrimaryKey
 
 			// detect if constraints are different
 			if !c.IsEqual(newFc) {
@@ -260,14 +234,6 @@ func (f *FieldConstraints) Add(newFc *FieldConstraint) error {
 			(*f)[i] = newFc
 			return nil
 		}
-
-		// ensure we don't have duplicate primary keys
-		if c.IsPrimaryKey && newFc.IsPrimaryKey {
-			return stringutil.Errorf(
-				"multiple primary keys are not allowed (%q is primary key)",
-				c.Path.String(),
-			)
-		}
 	}
 
 	err := f.validateDefaultValue(newFc)
@@ -293,7 +259,7 @@ func (f *FieldConstraints) validateDefaultValue(newFc *FieldConstraint) error {
 	// ensure default value type is compatible
 	if newFc.DefaultValue != nil && !newFc.Type.IsAny() {
 		// first, try to evaluate the default value
-		v, err := newFc.DefaultValue.Eval(nil)
+		v, err := newFc.DefaultValue.Eval(nil, nil)
 		// if there is no error, check if the default value can be converted to the type of the constraint
 		if err == nil {
 			_, err = document.CastAs(v, newFc.Type)
@@ -316,13 +282,7 @@ func (f *FieldConstraints) validateDefaultValue(newFc *FieldConstraint) error {
 }
 
 // ValidateDocument calls Convert then ensures the document validates against the field constraints.
-func (f FieldConstraints) ValidateDocument(tx *Transaction, d types.Document) (*document.FieldBuffer, error) {
-	fb := document.NewFieldBuffer()
-	err := fb.Copy(d)
-	if err != nil {
-		return nil, err
-	}
-
+func (f FieldConstraints) ValidateDocument(tx *Transaction, fb *document.FieldBuffer) (*document.FieldBuffer, error) {
 	// generate default values for all fields
 	for _, fc := range f {
 		if fc.DefaultValue == nil {
@@ -338,7 +298,7 @@ func (f FieldConstraints) ValidateDocument(tx *Transaction, d types.Document) (*
 			return nil, err
 		}
 
-		v, err := fc.DefaultValue.Eval(tx)
+		v, err := fc.DefaultValue.Eval(tx, nil)
 		if err != nil {
 			return nil, err
 		}
@@ -348,7 +308,7 @@ func (f FieldConstraints) ValidateDocument(tx *Transaction, d types.Document) (*
 		}
 	}
 
-	fb, err = f.ConvertDocument(fb)
+	fb, err := f.ConvertDocument(fb)
 	if err != nil {
 		return nil, err
 	}
@@ -475,26 +435,9 @@ func (f FieldConstraints) convertArrayAtPath(path document.Path, a types.Array,
 	return vb, err
 }
 
-type FieldConstraintIdentity struct {
-	SequenceName string
-	Always       bool
-}
-
-func (f *FieldConstraintIdentity) IsEqual(other *FieldConstraintIdentity) bool {
-	if f == nil {
-		return other == nil
-	}
-
-	if other == nil {
-		return false
-	}
-
-	return f.SequenceName == other.SequenceName && f.Always == other.Always
-}
-
 type TableExpression interface {
 	Bind(catalog *Catalog)
-	Eval(tx *Transaction) (types.Value, error)
+	Eval(tx *Transaction, d types.Document) (types.Value, error)
 	IsEqual(other TableExpression) bool
 	String() string
 }
@@ -503,7 +446,7 @@ type inferredTableExpression struct {
 	v types.Value
 }
 
-func (t *inferredTableExpression) Eval(tx *Transaction) (types.Value, error) {
+func (t *inferredTableExpression) Eval(tx *Transaction, d types.Document) (types.Value, error) {
 	return t.v, nil
 }
 
@@ -527,3 +470,139 @@ func (t *inferredTableExpression) IsEqual(other TableExpression) bool {
 func (t *inferredTableExpression) String() string {
 	return stringutil.Sprintf("%s", t.v)
 }
+
+// A TableConstraint represent a constraint specific to a table
+// and not necessarily to a single field path.
+type TableConstraint struct {
+	Name       string
+	Path       document.Path
+	Check      TableExpression
+	Unique     bool
+	PrimaryKey bool
+}
+
+// IsEqual compares t with other member by member.
+func (t *TableConstraint) IsEqual(other *TableConstraint) bool {
+	if t == nil {
+		return other == nil
+	}
+	if other == nil {
+		return false
+	}
+	return t.Name == other.Name && t.Path.IsEqual(other.Path) && t.Check.IsEqual(other.Check) && t.Unique == other.Unique && t.PrimaryKey == other.PrimaryKey
+}
+
+func (t *TableConstraint) String() string {
+	if t.Check != nil {
+		return stringutil.Sprintf("CHECK (%s)", t.Check)
+	}
+
+	if t.PrimaryKey {
+		return stringutil.Sprintf("PRIMARY KEY (%s)", t.Path)
+	}
+
+	if t.Unique {
+		return stringutil.Sprintf("UNIQUE (%s)", t.Path)
+	}
+
+	return ""
+}
+
+// TableConstraints holds the list of CHECK constraints.
+type TableConstraints []*TableConstraint
+
+// ValidateDocument checks all the table constraint for the given document.
+func (t *TableConstraints) ValidateDocument(tx *Transaction, fb *document.FieldBuffer) error {
+	for _, tc := range *t {
+		if tc.Check == nil {
+			continue
+		}
+
+		v, err := tc.Check.Eval(tx, fb)
+		if err != nil {
+			return err
+		}
+		var ok bool
+		switch v.Type() {
+		case types.BoolValue:
+			ok = v.V().(bool)
+		case types.IntegerValue:
+			ok = v.V().(int64) != 0
+		case types.DoubleValue:
+			ok = v.V().(float64) != 0
+		case types.NullValue:
+			ok = true
+		}
+
+		if !ok {
+			return stringutil.Errorf("document violates check constraint %q", tc.Name)
+		}
+	}
+
+	return nil
+}
+
+func (t *TableConstraints) AddCheck(tableName string, e TableExpression) {
+	var i int
+	for _, tc := range *t {
+		if tc.Check != nil {
+			i++
+		}
+	}
+
+	name := tableName + "_" + "check"
+	if i > 0 {
+		name += strconv.Itoa(i)
+	}
+
+	*t = append(*t, &TableConstraint{
+		Name:  name,
+		Check: e,
+	})
+}
+
+func (t *TableConstraints) AddPrimaryKey(tableName string, p document.Path) error {
+	for _, tc := range *t {
+		if tc.PrimaryKey {
+			return stringutil.Errorf("multiple primary keys for table %q are not allowed", tableName)
+		}
+	}
+
+	*t = append(*t, &TableConstraint{
+		Path:       p,
+		PrimaryKey: true,
+	})
+
+	return nil
+}
+
+// AddUnique adds a unique constraint to the table.
+// If the constraint is already present, it is ignored.
+func (t *TableConstraints) AddUnique(p document.Path) {
+	for _, tc := range *t {
+		if tc.Unique && tc.Path.IsEqual(p) {
+			return
+		}
+	}
+
+	*t = append(*t, &TableConstraint{
+		Path:   p,
+		Unique: true,
+	})
+}
+
+func (t *TableConstraints) Merge(other TableConstraints) error {
+	for _, tc := range other {
+		if tc.PrimaryKey {
+			if err := t.AddPrimaryKey(tc.Name, tc.Path); err != nil {
+				return err
+			}
+		} else if tc.Unique {
+			t.AddUnique(tc.Path)
+		} else if tc.Check != nil {
+			t.AddCheck(tc.Name, tc.Check)
+		}
+	}
+
+	return nil
+}
diff --git a/internal/database/info.go b/internal/database/info.go
index bd34fb7c..4e8b1163 100644
--- a/internal/database/info.go
+++ b/internal/database/info.go
@@ -18,6 +18,7 @@ type TableInfo struct {
 	ReadOnly  bool
 
 	FieldConstraints FieldConstraints
+	TableConstraints TableConstraints
 
 	// Name of the docid sequence if any.
 	DocidSequenceName string
@@ -39,15 +40,66 @@ func (ti *TableInfo) GenerateBaseName() string {
 	return ti.TableName
 }
 
+// ValidateDocument calls Convert then ensures the document validates against the field constraints.
+func (ti *TableInfo) ValidateDocument(tx *Transaction, d types.Document) (*document.FieldBuffer, error) {
+	fb := document.NewFieldBuffer()
+	err := fb.Copy(d)
+	if err != nil {
+		return nil, err
+	}
+
+	fb, err = ti.FieldConstraints.ValidateDocument(tx, fb)
+	if err != nil {
+		return nil, err
+	}
+
+	err = ti.TableConstraints.ValidateDocument(tx, fb)
+	if err != nil {
+		return nil, err
+	}
+
+	return fb, nil
+}
+
+func (ti *TableInfo) GetPrimaryKey() *FieldConstraint {
+	for _, tc := range ti.TableConstraints {
+		if tc.PrimaryKey == false {
+			continue
+		}
+
+		fc := ti.GetFieldConstraintForPath(tc.Path)
+		if fc == nil {
+			return &FieldConstraint{
+				Path: tc.Path,
+			}
+		}
+
+		return fc
+	}
+
+	return nil
+}
+
+func (ti *TableInfo) GetFieldConstraintForPath(p document.Path) *FieldConstraint {
+	for _, fc := range ti.FieldConstraints {
+		if fc.Path.IsEqual(p) {
+			return fc
+		}
+	}
+
+	return nil
+}
+
 // String returns a SQL representation.
 func (ti *TableInfo) String() string {
 	var s strings.Builder
 
 	stringutil.Fprintf(&s, "CREATE TABLE %s", stringutil.NormalizeIdentifier(ti.TableName, '`'))
-	if len(ti.FieldConstraints) > 0 {
+	if len(ti.FieldConstraints) > 0 || len(ti.TableConstraints) > 0 {
 		s.WriteString(" (")
 	}
 
+	var hasFieldConstraints bool
 	for i, fc := range ti.FieldConstraints {
 		if fc.IsInferred {
 			continue
@@ -58,9 +110,19 @@ func (ti *TableInfo) String() string {
 		}
 
 		s.WriteString(fc.String())
+
+		hasFieldConstraints = true
+	}
+
+	for i, tc := range ti.TableConstraints {
+		if i > 0 || hasFieldConstraints {
+			s.WriteString(", ")
+		}
+
+		s.WriteString(tc.String())
 	}
 
-	if len(ti.FieldConstraints) > 0 {
+	if len(ti.FieldConstraints) > 0 || len(ti.TableConstraints) > 0 {
 		s.WriteString(")")
 	}
 
@@ -71,7 +133,9 @@ func (ti *TableInfo) String() string {
 func (ti *TableInfo) Clone() *TableInfo {
 	cp := *ti
 	cp.FieldConstraints = nil
+	cp.TableConstraints = nil
 	cp.FieldConstraints = append(cp.FieldConstraints, ti.FieldConstraints...)
+	cp.TableConstraints = append(cp.TableConstraints, ti.TableConstraints...)
 	return &cp
 }
 
diff --git a/internal/database/sequence.go b/internal/database/sequence.go
index a52d5f23..cad70076 100644
--- a/internal/database/sequence.go
+++ b/internal/database/sequence.go
@@ -24,8 +24,7 @@ var sequenceTableInfo = &TableInfo{
 					FieldName: "name",
 				},
 			},
-			Type:         types.TextValue,
-			IsPrimaryKey: true,
+			Type: types.TextValue,
 		},
 		{
 			Path: document.Path{
@@ -36,6 +35,16 @@ var sequenceTableInfo = &TableInfo{
 			Type: types.IntegerValue,
 		},
 	},
+	TableConstraints: []*TableConstraint{
+		{
+			Path: document.Path{
+				document.PathFragment{
+					FieldName: "name",
+				},
+			},
+			PrimaryKey: true,
+		},
+	},
 }
 
 // A Sequence manages a sequence of numbers.
diff --git a/internal/database/table.go b/internal/database/table.go
index 46221032..208703c4 100644
--- a/internal/database/table.go
+++ b/internal/database/table.go
@@ -59,7 +59,7 @@ func (t *Table) InsertWithConflictResolution(d types.Document, onConflict OnInse
 		return nil, errors.New("cannot write to read-only table")
 	}
 
-	fb, err := t.Info.FieldConstraints.ValidateDocument(t.Tx, d)
+	fb, err := t.Info.ValidateDocument(t.Tx, d)
 	if err != nil {
 		if onConflict != nil {
 			if ce, ok := err.(*ConstraintViolationError); ok && ce.Constraint == "NOT NULL" {
@@ -156,7 +156,7 @@ func (t *Table) InsertWithConflictResolution(d types.Document, onConflict OnInse
 	return documentWithKey{
 		Document: fb,
 		key:      key,
-		pk:       t.Info.FieldConstraints.GetPrimaryKey(),
+		pk:       t.Info.GetPrimaryKey(),
 	}, nil
 }
 
@@ -229,7 +229,7 @@ func (t *Table) Replace(key []byte, d types.Document) (types.Document, error) {
 		return nil, errors.New("cannot write to read-only table")
 	}
 
-	d, err := t.Info.FieldConstraints.ValidateDocument(t.Tx, d)
+	d, err := t.Info.ValidateDocument(t.Tx, d)
 	if err != nil {
 		return nil, err
 	}
@@ -428,7 +428,7 @@ func (t *Table) EncodeValue(v types.Value) ([]byte, error) {
 func (t *Table) encodeValueToKey(info *TableInfo, v types.Value) ([]byte, error) {
 	var err error
 
-	pk := t.Info.FieldConstraints.GetPrimaryKey()
+	pk := t.Info.GetPrimaryKey()
 	if pk == nil {
 		// if no primary key was defined, convert the pivot to an integer then to an unsigned integer
 		// and encode it as a varint
@@ -502,7 +502,7 @@ func (t *Table) iterate(pivot types.Value, reverse bool, fn func(d types.Documen
 		codec: t.Codec,
 	}
 
-	d.pk = t.Info.FieldConstraints.GetPrimaryKey()
+	d.pk = t.Info.GetPrimaryKey()
 
 	it := t.Store.Iterator(engine.IteratorOptions{Reverse: reverse})
 	defer it.Close()
@@ -538,7 +538,7 @@ func (t *Table) GetDocument(key []byte) (types.Document, error) {
 	var d documentWithKey
 	d.Document = t.Codec.NewDecoder(v)
 	d.key = key
-	d.pk = t.Info.FieldConstraints.GetPrimaryKey()
+	d.pk = t.Info.GetPrimaryKey()
 	return &d, err
 }
 
@@ -549,7 +549,7 @@ func (t *Table) GetDocument(key []byte) (types.Document, error) {
 // if there are no primary key in the table, a default
 // key is generated, called the docid.
 func (t *Table) generateKey(info *TableInfo, d types.Document) ([]byte, error) {
-	if pk := t.Info.FieldConstraints.GetPrimaryKey(); pk != nil {
+	if pk := t.Info.GetPrimaryKey(); pk != nil {
 		v, err := pk.Path.GetValueFromDocument(d)
 		if errors.Is(err, document.ErrFieldNotFound) {
 			return nil, stringutil.Errorf("missing primary key at path %q", pk.Path)
diff --git a/internal/expr/constraint.go b/internal/expr/constraint.go
index 950d4fd9..d3324c9e 100644
--- a/internal/expr/constraint.go
+++ b/internal/expr/constraint.go
@@ -18,10 +18,11 @@ func Constraint(e Expr) *ConstraintExpr {
 	}
 }
 
-func (t *ConstraintExpr) Eval(tx *database.Transaction) (types.Value, error) {
+func (t *ConstraintExpr) Eval(tx *database.Transaction, d types.Document) (types.Value, error) {
 	var env environment.Environment
 	env.Catalog = t.Catalog
 	env.Tx = tx
+	env.SetDocument(d)
 
 	if t.Expr == nil {
 		return NullLiteral, errors.New("missing expression")
diff --git a/internal/planner/index_selection.go b/internal/planner/index_selection.go
index 19bf7bdb..26da3bc0 100644
--- a/internal/planner/index_selection.go
+++ b/internal/planner/index_selection.go
@@ -219,7 +219,7 @@ func (i *indexSelector) isFilterIndexable(f *stream.FilterOperator) *filterNode
 
 func (i *indexSelector) associatePkWithNodes(tb *database.TableInfo, nodes filterNodes) *candidate {
 	// TODO: add support for the pk() function
-	pk := tb.FieldConstraints.GetPrimaryKey()
+	pk := tb.GetPrimaryKey()
 
 	if pk == nil {
 		return nil
diff --git a/internal/query/statement/alter.go b/internal/query/statement/alter.go
index b2d8e1a7..f22a4dda 100644
--- a/internal/query/statement/alter.go
+++ b/internal/query/statement/alter.go
@@ -39,8 +39,7 @@ func (stmt AlterStmt) Run(ctx *Context) (Result, error) {
 }
 
 type AlterTableAddField struct {
-	TableName  string
-	Constraint database.FieldConstraint
+	Info database.TableInfo
 }
 
 // IsReadOnly always returns false. It implements the Statement interface.
@@ -53,14 +52,11 @@ func (stmt AlterTableAddField) IsReadOnly() bool {
 func (stmt AlterTableAddField) Run(ctx *Context) (Result, error) {
 	var res Result
 
-	if stmt.TableName == "" {
-		return res, errors.New("missing table name")
-	}
-
-	if stmt.Constraint.Path == nil {
-		return res, errors.New("missing field name")
+	var fc *database.FieldConstraint
+	if stmt.Info.FieldConstraints != nil {
+		fc = stmt.Info.FieldConstraints[0]
 	}
 
-	err := ctx.Catalog.AddFieldConstraint(ctx.Tx, stmt.TableName, stmt.Constraint)
+	err := ctx.Catalog.AddFieldConstraint(ctx.Tx, stmt.Info.TableName, fc, stmt.Info.TableConstraints)
 	return res, err
 }
diff --git a/internal/query/statement/create.go b/internal/query/statement/create.go
index 32f1eb76..8617c57d 100644
--- a/internal/query/statement/create.go
+++ b/internal/query/statement/create.go
@@ -26,7 +26,7 @@ func (stmt *CreateTableStmt) Run(ctx *Context) (Result, error) {
 	var res Result
 
 	// if there is no primary key, create a docid sequence
-	if stmt.Info.FieldConstraints.GetPrimaryKey() == nil {
+	if stmt.Info.GetPrimaryKey() == nil {
 		seq := database.SequenceInfo{
 			IncrementBy: 1,
 			Min:         1, Max: math.MaxInt64,
@@ -52,16 +52,21 @@ func (stmt *CreateTableStmt) Run(ctx *Context) (Result, error) {
 	}
 
 	// create a unique index for every unique constraint
-	for _, fc := range stmt.Info.FieldConstraints {
-		if fc.IsUnique {
+	for _, tc := range stmt.Info.TableConstraints {
+		if tc.Unique {
+			fc := stmt.Info.GetFieldConstraintForPath(tc.Path)
+			var tp types.ValueType
+			if fc != nil {
+				tp = fc.Type
+			}
 			err = ctx.Catalog.CreateIndex(ctx.Tx, &database.IndexInfo{
 				TableName: stmt.Info.TableName,
-				Paths:     []document.Path{fc.Path},
+				Paths:     []document.Path{tc.Path},
 				Unique:    true,
-				Types:     []types.ValueType{fc.Type},
+				Types:     []types.ValueType{tp},
 				Owner: database.Owner{
 					TableName: stmt.Info.TableName,
-					Path:      fc.Path,
+					Path:      tc.Path,
 				},
 			})
 			if err != nil {
diff --git a/internal/query/statement/drop.go b/internal/query/statement/drop.go
index 7884cda6..0fccc5d9 100644
--- a/internal/query/statement/drop.go
+++ b/internal/query/statement/drop.go
@@ -41,7 +41,7 @@ func (stmt DropTableStmt) Run(ctx *Context) (Result, error) {
 	}
 
 	// if there is no primary key, drop the docid sequence
-	if tb.Info.FieldConstraints.GetPrimaryKey() == nil {
+	if tb.Info.GetPrimaryKey() == nil {
 		err = ctx.Catalog.DropSequence(ctx.Tx, tb.Info.DocidSequenceName)
 		if err != nil {
 			return res, err
diff --git a/internal/sql/parser/alter.go b/internal/sql/parser/alter.go
index 23adb724..b347f0ed 100644
--- a/internal/sql/parser/alter.go
+++ b/internal/sql/parser/alter.go
@@ -1,6 +1,7 @@
 package parser
 
 import (
+	"github.com/genjidb/genji/internal/database"
 	"github.com/genjidb/genji/internal/query/statement"
 	"github.com/genjidb/genji/internal/sql/scanner"
 )
@@ -25,23 +26,31 @@ func (p *Parser) parseAlterTableRenameStatement(tableName string) (_ statement.A
 
 func (p *Parser) parseAlterTableAddFieldStatement(tableName string) (_ statement.AlterTableAddField, err error) {
 	var stmt statement.AlterTableAddField
-	stmt.TableName = tableName
+	stmt.Info.TableName = tableName
 
 	// Parse "FIELD".
 	if err := p.parseTokens(scanner.FIELD); err != nil {
 		return stmt, err
 	}
 
+	var fc database.FieldConstraint
 	// Parse new field definition.
-	err = p.parseFieldDefinition(&stmt.Constraint)
+	err = p.parseFieldDefinition(&fc, &stmt.Info)
 	if err != nil {
 		return stmt, err
 	}
 
-	if stmt.Constraint.IsPrimaryKey {
+	if stmt.Info.GetPrimaryKey() != nil {
 		return stmt, &ParseError{Message: "cannot add a PRIMARY KEY constraint"}
 	}
 
+	if !fc.IsEmpty() {
+		err = stmt.Info.FieldConstraints.Add(&fc)
+		if err != nil {
+			return stmt, err
+		}
+	}
+
 	return stmt, nil
 }
 
diff --git a/internal/sql/parser/create.go b/internal/sql/parser/create.go
index 85a09512..da00c9c5 100644
--- a/internal/sql/parser/create.go
+++ b/internal/sql/parser/create.go
@@ -59,30 +59,6 @@ func (p *Parser) parseCreateTableStatement() (*statement.CreateTableStmt, error)
 	return &stmt, err
 }
 
-func (p *Parser) parseFieldDefinition(fc *database.FieldConstraint) (err error) {
-	fc.Path, err = p.parsePath()
-	if err != nil {
-		return err
-	}
-
-	fc.Type, err = p.parseType()
-	if err != nil {
-		p.Unscan()
-	}
-
-	err = p.parseFieldConstraint(fc)
-	if err != nil {
-		return err
-	}
-
-	if fc.Type.IsAny() && fc.DefaultValue == nil && !fc.IsNotNull && !fc.IsPrimaryKey && !fc.IsUnique {
-		tok, pos, lit := p.ScanIgnoreWhitespace()
-		return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", "TYPE"}, pos)
-	}
-
-	return nil
-}
-
 func (p *Parser) parseConstraints(stmt *statement.CreateTableStmt) error {
 	// Parse ( token.
 	if ok, err := p.parseOptional(scanner.LPAREN); !ok || err != nil {
@@ -116,15 +92,17 @@ func (p *Parser) parseConstraints(stmt *statement.CreateTableStmt) error {
 		// if set to false, we are still parsing field definitions
 		if !parsingTableConstraints {
 			var fc database.FieldConstraint
-
-			err = p.parseFieldDefinition(&fc)
+			err := p.parseFieldDefinition(&fc, &stmt.Info)
 			if err != nil {
 				return err
 			}
 
-			err = stmt.Info.FieldConstraints.Add(&fc)
-			if err != nil {
-				return err
+			// if the field definition is empty, we ignore it
+			if !fc.IsEmpty() {
+				err = stmt.Info.FieldConstraints.Add(&fc)
+				if err != nil {
+					return err
+				}
 			}
 		}
 
@@ -139,22 +117,25 @@ func (p *Parser) parseConstraints(stmt *statement.CreateTableStmt) error {
 		return err
 	}
 
-	// ensure only one primary key
-	var pkFound bool
-	for _, fc := range stmt.Info.FieldConstraints {
-		if fc.IsPrimaryKey {
-			if pkFound {
-				return stringutil.Errorf("table %q has more than one primary key", stmt.Info.TableName)
-			}
+	return nil
+}
 
-			pkFound = true
-		}
+func (p *Parser) parseFieldDefinition(fc *database.FieldConstraint, info *database.TableInfo) error {
+	var err error
+
+	fc.Path, err = p.parsePath()
+	if err != nil {
+		return err
 	}
 
-	return nil
-}
+	fc.Type, err = p.parseType()
+	if err != nil {
+		p.Unscan()
+	}
+
+	var addedTc int
 
-func (p *Parser) parseFieldConstraint(fc *database.FieldConstraint) error {
+LOOP:
 	for {
 		tok, pos, lit := p.ScanIgnoreWhitespace()
 		switch tok {
@@ -164,12 +145,11 @@ func (p *Parser) parseFieldConstraint(fc *database.FieldConstraint) error {
 				return err
 			}
 
-			// if it's already a primary key we return an error
-			if fc.IsPrimaryKey {
-				return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", ")"}, pos)
+			err = info.TableConstraints.AddPrimaryKey(info.TableName, fc.Path)
+			if err != nil {
+				return err
 			}
-
-			fc.IsPrimaryKey = true
+			addedTc++
 		case scanner.NOT:
 			// Parse "NULL"
 			if err := p.parseTokens(scanner.NULL); err != nil {
@@ -234,17 +214,42 @@ func (p *Parser) parseFieldConstraint(fc *database.FieldConstraint) error {
 				}
 			}
 		case scanner.UNIQUE:
-			// if it's already unique we return an error
-			if fc.IsUnique {
-				return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", ")"}, pos)
+			info.TableConstraints.AddUnique(fc.Path)
+			addedTc++
+		case scanner.CHECK:
+			// Parse "("
+			err := p.parseTokens(scanner.LPAREN)
+			if err != nil {
+				return err
+			}
+
+			e, err := p.ParseExpr()
+			if err != nil {
+				return err
 			}
 
-			fc.IsUnique = true
+			// Parse ")"
+			err = p.parseTokens(scanner.RPAREN)
+			if err != nil {
+				return err
+			}
+
+			info.TableConstraints.AddCheck(info.TableName, expr.Constraint(e))
+			addedTc++
 		default:
 			p.Unscan()
-			return nil
+			break LOOP
 		}
 	}
+
+	// if no constraint was added we return an error. i.e:
+	// CREATE TABLE t (a)
+	if fc.IsEmpty() && addedTc == 0 {
+		tok, pos, lit := p.ScanIgnoreWhitespace()
+		return newParseError(scanner.Tokstr(tok, lit), []string{"CONSTRAINT", "TYPE"}, pos)
+	}
+
+	return nil
 }
 
 func (p *Parser) parseTableConstraint(stmt *statement.CreateTableStmt) (bool, error) {
@@ -270,20 +275,8 @@ func (p *Parser) parseTableConstraint(stmt *statement.CreateTableStmt) (bool, er
 			return false, err
 		}
 
-		if pk := stmt.Info.FieldConstraints.GetPrimaryKey(); pk != nil {
-			return false, stringutil.Errorf("table %q has more than one primary key", stmt.Info.TableName)
-		}
-		fc := stmt.Info.FieldConstraints.Get(primaryKeyPath)
-		if fc == nil {
-			err = stmt.Info.FieldConstraints.Add(&database.FieldConstraint{
-				Path:         primaryKeyPath,
-				IsPrimaryKey: true,
-			})
-			if err != nil {
-				return false, err
-			}
-		} else {
-			fc.IsPrimaryKey = true
+		if err := stmt.Info.TableConstraints.AddPrimaryKey(stmt.Info.TableName, primaryKeyPath); err != nil {
+			return false, err
 		}
 
 		return true, nil
@@ -305,19 +298,28 @@ func (p *Parser) parseTableConstraint(stmt *statement.CreateTableStmt) (bool, er
 			return false, err
 		}
 
-		fc := stmt.Info.FieldConstraints.Get(uniquePath)
-		if fc == nil {
-			err = stmt.Info.FieldConstraints.Add(&database.FieldConstraint{
-				Path:     uniquePath,
-				IsUnique: true,
-			})
-			if err != nil {
-				return false, err
-			}
-		} else {
-			fc.IsUnique = true
+		stmt.Info.TableConstraints.AddUnique(uniquePath)
+		return true, nil
+	case scanner.CHECK:
+		// Parse "("
+		err = p.parseTokens(scanner.LPAREN)
+		if err != nil {
+			return false, err
 		}
 
+		e, err := p.ParseExpr()
+		if err != nil {
+			return false, err
+		}
+
+		// Parse ")"
+		err = p.parseTokens(scanner.RPAREN)
+		if err != nil {
+			return false, err
+		}
+
+		stmt.Info.TableConstraints.AddCheck(stmt.Info.TableName, expr.Constraint(e))
+
 		return true, nil
 	default:
 		p.Unscan()
diff --git a/internal/sql/parser/select.go b/internal/sql/parser/select.go
index 5bb2115d..74f4c03d 100644
--- a/internal/sql/parser/select.go
+++ b/internal/sql/parser/select.go
@@ -114,7 +114,7 @@ func (p *Parser) parseSelectCore() (*statement.StreamStmt, error) {
 		return nil, err
 	}
 
-	stmt.Distinct, err = p.parseDistinct()
+	stmt.Distinct, err = p.parseOptional(scanner.DISTINCT)
 	if err != nil {
 		return nil, err
 	}
@@ -202,15 +202,6 @@ func (p *Parser) parseProjectedExpr() (expr.Expr, error) {
 	return ne, nil
 }
 
-func (p *Parser) parseDistinct() (bool, error) {
-	if tok, _, _ := p.ScanIgnoreWhitespace(); tok != scanner.DISTINCT {
-		p.Unscan()
-		return false, nil
-	}
-
-	return true, nil
-}
-
 func (p *Parser) parseFrom() (string, error) {
 	if ok, err := p.parseOptional(scanner.FROM); !ok || err != nil {
 		return "", err
diff --git a/internal/sql/scanner/token.go b/internal/sql/scanner/token.go
index b2d737c3..3c7853e8 100644
--- a/internal/sql/scanner/token.go
+++ b/internal/sql/scanner/token.go
@@ -85,6 +85,7 @@ const (
 	BY
 	CACHE
 	CAST
+	CHECK
 	COMMIT
 	CONFLICT
 	CREATE
@@ -229,6 +230,7 @@ var tokens = [...]string{
 	BY:          "BY",
 	CACHE:       "CACHE",
 	CAST:        "CAST",
+	CHECK:       "CHECK",
 	COMMIT:      "COMMIT",
 	CONFLICT:    "CONFLICT",
 	CREATE:      "CREATE",
diff --git a/internal/stream/range.go b/internal/stream/range.go
index a823d12c..521a25d8 100644
--- a/internal/stream/range.go
+++ b/internal/stream/range.go
@@ -28,7 +28,7 @@ type ValueRange struct {
 func (r *ValueRange) evalRange(table *database.Table, env *environment.Environment) (*encodedValueRange, bool, error) {
 	var err error
 
-	pk := table.Info.FieldConstraints.GetPrimaryKey()
+	pk := table.Info.GetPrimaryKey()
 
 	rng := encodedValueRange{
 		pkType:      pk.Type,


Test patch:
diff --git a/cmd/genji/dbutil/dump_test.go b/cmd/genji/dbutil/dump_test.go
index 34ec5513..1559f8a1 100644
--- a/cmd/genji/dbutil/dump_test.go
+++ b/cmd/genji/dbutil/dump_test.go
@@ -137,7 +137,7 @@ func TestDumpSchema(t *testing.T) {
 					writeToBuf("\n")
 				}
 
-				q := fmt.Sprintf("CREATE TABLE %s (a INTEGER UNIQUE);", table)
+				q := fmt.Sprintf("CREATE TABLE %s (a INTEGER, UNIQUE (a));", table)
 				err = db.Exec(q)
 				assert.NoError(t, err)
 				writeToBuf(q + "\n")
diff --git a/internal/database/catalog_test.go b/internal/database/catalog_test.go
index 1833f88a..72530f44 100644
--- a/internal/database/catalog_test.go
+++ b/internal/database/catalog_test.go
@@ -11,6 +11,7 @@ import (
 	errs "github.com/genjidb/genji/errors"
 	"github.com/genjidb/genji/internal/database"
 	"github.com/genjidb/genji/internal/errors"
+	"github.com/genjidb/genji/internal/expr"
 	"github.com/genjidb/genji/internal/testutil"
 	"github.com/genjidb/genji/internal/testutil/assert"
 	"github.com/genjidb/genji/types"
@@ -112,12 +113,15 @@ func TestCatalogTable(t *testing.T) {
 		db, cleanup := testutil.NewTestDB(t)
 		defer cleanup()
 
-		ti := &database.TableInfo{FieldConstraints: []*database.FieldConstraint{
-			{Path: testutil.ParseDocumentPath(t, "name"), Type: types.TextValue, IsNotNull: true},
-			{Path: testutil.ParseDocumentPath(t, "age"), Type: types.IntegerValue, IsPrimaryKey: true},
-			{Path: testutil.ParseDocumentPath(t, "gender"), Type: types.TextValue},
-			{Path: testutil.ParseDocumentPath(t, "city"), Type: types.TextValue},
-		}}
+		ti := &database.TableInfo{
+			FieldConstraints: []*database.FieldConstraint{
+				{Path: testutil.ParseDocumentPath(t, "name"), Type: types.TextValue, IsNotNull: true},
+				{Path: testutil.ParseDocumentPath(t, "age"), Type: types.IntegerValue},
+				{Path: testutil.ParseDocumentPath(t, "gender"), Type: types.TextValue},
+				{Path: testutil.ParseDocumentPath(t, "city"), Type: types.TextValue},
+			}, TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "age"), PrimaryKey: true},
+			}}
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
 			err := catalog.CreateTable(tx, "foo", ti)
@@ -194,9 +198,11 @@ func TestCatalogTable(t *testing.T) {
 
 		ti := &database.TableInfo{FieldConstraints: []*database.FieldConstraint{
 			{Path: testutil.ParseDocumentPath(t, "name"), Type: types.TextValue, IsNotNull: true},
-			{Path: testutil.ParseDocumentPath(t, "age"), Type: types.IntegerValue, IsPrimaryKey: true},
+			{Path: testutil.ParseDocumentPath(t, "age"), Type: types.IntegerValue},
 			{Path: testutil.ParseDocumentPath(t, "gender"), Type: types.TextValue},
 			{Path: testutil.ParseDocumentPath(t, "city"), Type: types.TextValue},
+		}, TableConstraints: []*database.TableConstraint{
+			{Path: testutil.ParseDocumentPath(t, "age"), PrimaryKey: true},
 		}}
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
@@ -211,7 +217,10 @@ func TestCatalogTable(t *testing.T) {
 			fieldToAdd := database.FieldConstraint{
 				Path: testutil.ParseDocumentPath(t, "last_name"), Type: types.TextValue,
 			}
-			err := catalog.AddFieldConstraint(tx, "foo", fieldToAdd)
+			// Add table constraint
+			var tcs database.TableConstraints
+			tcs.AddCheck("foo", expr.Constraint(testutil.ParseExpr(t, "last_name > first_name")))
+			err := catalog.AddFieldConstraint(tx, "foo", &fieldToAdd, tcs)
 			assert.NoError(t, err)
 
 			tb, err := catalog.GetTable(tx, "foo")
@@ -220,22 +229,22 @@ func TestCatalogTable(t *testing.T) {
 			// The field constraints should not be the same.
 
 			require.Contains(t, tb.Info.FieldConstraints, &fieldToAdd)
+			require.Equal(t, expr.Constraint(testutil.ParseExpr(t, "last_name > first_name")), tb.Info.TableConstraints[1].Check)
 
 			// Renaming a non existing table should return an error
-			err = catalog.AddFieldConstraint(tx, "bar", fieldToAdd)
+			err = catalog.AddFieldConstraint(tx, "bar", &fieldToAdd, nil)
 			if !errors.Is(err, errs.NotFoundError{}) {
 				assert.ErrorIs(t, err, errs.NotFoundError{Name: "bar"})
 			}
 
 			// Adding a existing field should return an error
-			err = catalog.AddFieldConstraint(tx, "foo", *ti.FieldConstraints[0])
+			err = catalog.AddFieldConstraint(tx, "foo", ti.FieldConstraints[0], nil)
 			assert.Error(t, err)
 
 			// Adding a second primary key should return an error
-			fieldToAdd = database.FieldConstraint{
-				Path: testutil.ParseDocumentPath(t, "foobar"), Type: types.IntegerValue, IsPrimaryKey: true,
-			}
-			err = catalog.AddFieldConstraint(tx, "foo", fieldToAdd)
+			tcs = nil
+			tcs.AddPrimaryKey("foo", testutil.ParseDocumentPath(t, "address"))
+			err = catalog.AddFieldConstraint(tx, "foo", nil, tcs)
 			assert.Error(t, err)
 
 			return errDontCommit
@@ -311,8 +320,8 @@ func TestCatalogCreateIndex(t *testing.T) {
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
 			err := catalog.CreateTable(tx, "test", &database.TableInfo{
-				FieldConstraints: database.FieldConstraints{
-					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				TableConstraints: []*database.TableConstraint{
+					{Path: testutil.ParseDocumentPath(t, "a"), PrimaryKey: true},
 				},
 			})
 			if err != nil {
@@ -493,8 +502,8 @@ func TestCatalogReIndex(t *testing.T) {
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
 			err := catalog.CreateTable(tx, "test", &database.TableInfo{
-				FieldConstraints: database.FieldConstraints{
-					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				TableConstraints: []*database.TableConstraint{
+					{Path: testutil.ParseDocumentPath(t, "a"), PrimaryKey: true},
 				},
 			})
 			assert.NoError(t, err)
@@ -545,8 +554,8 @@ func TestCatalogReIndex(t *testing.T) {
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
 			err := catalog.CreateTable(tx, "test", &database.TableInfo{
-				FieldConstraints: database.FieldConstraints{
-					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				TableConstraints: []*database.TableConstraint{
+					{Path: testutil.ParseDocumentPath(t, "a"), PrimaryKey: true},
 				},
 			})
 			assert.NoError(t, err)
@@ -640,8 +649,8 @@ func TestReIndexAll(t *testing.T) {
 
 		updateCatalog(t, db, func(tx *database.Transaction, catalog *database.Catalog) error {
 			err := catalog.CreateTable(tx, "test1", &database.TableInfo{
-				FieldConstraints: database.FieldConstraints{
-					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				TableConstraints: []*database.TableConstraint{
+					{Path: testutil.ParseDocumentPath(t, "a"), PrimaryKey: true},
 				},
 			})
 			assert.NoError(t, err)
@@ -649,8 +658,8 @@ func TestReIndexAll(t *testing.T) {
 			assert.NoError(t, err)
 
 			err = catalog.CreateTable(tx, "test2", &database.TableInfo{
-				FieldConstraints: database.FieldConstraints{
-					{Path: testutil.ParseDocumentPath(t, "a"), IsPrimaryKey: true},
+				TableConstraints: []*database.TableConstraint{
+					{Path: testutil.ParseDocumentPath(t, "a"), PrimaryKey: true},
 				},
 			})
 			assert.NoError(t, err)
@@ -759,11 +768,11 @@ func TestReadOnlyTables(t *testing.T) {
 	err = res.Iterate(func(d types.Document) error {
 		switch i {
 		case 0:
-			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT PRIMARY KEY, seq INTEGER)", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT, seq INTEGER, PRIMARY KEY (name))", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
 		case 1:
 			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_store_seq", "owner":{"table_name":"__genji_catalog"}, "sql":"CREATE SEQUENCE __genji_store_seq CACHE 16", "type":"sequence"}`)
 		case 2:
-			testutil.RequireDocJSONEq(t, d, `{"name":"foo", "docid_sequence_name":"foo_seq", "sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE UNIQUE)", "store_name":"AQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"foo", "docid_sequence_name":"foo_seq", "sql":"CREATE TABLE foo (a INTEGER, b[3].c DOUBLE, UNIQUE (b[3].c))", "store_name":"AQ==", "type":"table"}`)
 		case 3:
 			testutil.RequireDocJSONEq(t, d, `{"name":"foo_b[3].c_idx", "owner":{"table_name":"foo", "path":"b[3].c"}, "sql":"CREATE UNIQUE INDEX `+"`foo_b[3].c_idx`"+` ON foo (b[3].c)", "store_name":"Ag==", "table_name":"foo", "type":"index"}`)
 		case 4:
diff --git a/internal/database/constraint_test.go b/internal/database/constraint_test.go
index 4ba9a84c..8224bc7d 100644
--- a/internal/database/constraint_test.go
+++ b/internal/database/constraint_test.go
@@ -43,18 +43,6 @@ func TestFieldConstraintsInfer(t *testing.T) {
 			},
 			false,
 		},
-		{
-			"Primary key",
-			[]*database.FieldConstraint{
-				{Path: document.NewPath("a"), Type: types.ArrayValue, IsPrimaryKey: true},
-				{Path: document.NewPath("a", "0"), Type: types.IntegerValue},
-			},
-			[]*database.FieldConstraint{
-				{Path: document.NewPath("a"), Type: types.ArrayValue, IsPrimaryKey: true},
-				{Path: document.NewPath("a", "0"), Type: types.IntegerValue},
-			},
-			false,
-		},
 		{
 			"Complex path",
 			[]*database.FieldConstraint{{Path: document.NewPath("a", "b", "3", "1", "c"), Type: types.IntegerValue}},
@@ -183,13 +171,6 @@ func TestFieldConstraintsAdd(t *testing.T) {
 			nil,
 			true,
 		},
-		{
-			"Duplicate primary key",
-			[]*database.FieldConstraint{{Path: document.NewPath("a"), IsPrimaryKey: true, Type: types.IntegerValue}},
-			database.FieldConstraint{Path: document.NewPath("b"), IsPrimaryKey: true, Type: types.IntegerValue},
-			nil,
-			true,
-		},
 		{
 			"Different path",
 			[]*database.FieldConstraint{{Path: document.NewPath("a"), Type: types.IntegerValue}},
diff --git a/internal/database/table_test.go b/internal/database/table_test.go
index fa5e5788..c80ce52d 100644
--- a/internal/database/table_test.go
+++ b/internal/database/table_test.go
@@ -244,7 +244,10 @@ func TestTableInsert(t *testing.T) {
 
 		err := db.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{Path: testutil.ParseDocumentPath(t, "foo.a[1]"), Type: types.IntegerValue, IsPrimaryKey: true},
+				{Path: testutil.ParseDocumentPath(t, "foo.a[1]"), Type: types.IntegerValue},
+			},
+			TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "foo.a[1]"), PrimaryKey: true},
 			},
 		})
 		assert.NoError(t, err)
@@ -306,7 +309,10 @@ func TestTableInsert(t *testing.T) {
 
 		err := db.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{Path: testutil.ParseDocumentPath(t, "foo"), Type: types.IntegerValue, IsPrimaryKey: true},
+				{Path: testutil.ParseDocumentPath(t, "foo"), Type: types.IntegerValue},
+			},
+			TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "foo"), PrimaryKey: true},
 			},
 		})
 		assert.NoError(t, err)
@@ -582,8 +588,12 @@ func TestTableInsert(t *testing.T) {
 		tb := createTable(t, tx, db.Catalog, database.TableInfo{
 			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{Path: testutil.ParseDocumentPath(t, "foo"), IsPrimaryKey: true, IsNotNull: true},
-			}})
+				{Path: testutil.ParseDocumentPath(t, "foo"), IsNotNull: true},
+			},
+			TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "foo"), PrimaryKey: true},
+			},
+		})
 
 		doc := document.NewFieldBuffer().
 			Add("foo", types.NewIntegerValue(10))
@@ -604,8 +614,12 @@ func TestTableInsert(t *testing.T) {
 		tb := createTable(t, tx, db.Catalog, database.TableInfo{
 			TableName: "test",
 			FieldConstraints: []*database.FieldConstraint{
-				{Path: testutil.ParseDocumentPath(t, "foo"), IsPrimaryKey: true, IsNotNull: true},
-			}})
+				{Path: testutil.ParseDocumentPath(t, "foo"), IsNotNull: true},
+			},
+			TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "foo"), PrimaryKey: true},
+			},
+		})
 
 		doc := document.NewFieldBuffer().
 			Add("foo", types.NewIntegerValue(10))
@@ -723,8 +737,12 @@ func TestTableInsert(t *testing.T) {
 
 		err := db.Catalog.CreateTable(tx, "test", &database.TableInfo{
 			FieldConstraints: []*database.FieldConstraint{
-				{Path: testutil.ParseDocumentPath(t, "foo"), IsPrimaryKey: true, IsNotNull: true},
-			}})
+				{Path: testutil.ParseDocumentPath(t, "foo"), IsNotNull: true},
+			},
+			TableConstraints: []*database.TableConstraint{
+				{Path: testutil.ParseDocumentPath(t, "foo"), PrimaryKey: true},
+			},
+		})
 		assert.NoError(t, err)
 
 		tb, err := db.Catalog.GetTable(tx, "test")
diff --git a/internal/query/statement/create_test.go b/internal/query/statement/create_test.go
index efffcb5a..bb476b6f 100644
--- a/internal/query/statement/create_test.go
+++ b/internal/query/statement/create_test.go
@@ -106,7 +106,7 @@ func TestCreateTable(t *testing.T) {
 					InferredBy: []document.Path{
 						parsePath(t, "foo.bar[1].hello"),
 					}},
-				{Path: parsePath(t, "foo.bar[1].hello"), Type: types.BlobValue, IsPrimaryKey: true},
+				{Path: parsePath(t, "foo.bar[1].hello"), Type: types.BlobValue},
 				{Path: parsePath(t, "foo.a"), Type: types.ArrayValue, IsInferred: true,
 					InferredBy: []document.Path{
 						parsePath(t, "foo.a[1][2]"),
@@ -164,7 +164,7 @@ func TestCreateTable(t *testing.T) {
 					InferredBy: []document.Path{
 						parsePath(t, "foo.bar[1].hello"),
 					}},
-				{Path: parsePath(t, "foo.bar[1].hello"), Type: types.BlobValue, IsPrimaryKey: true},
+				{Path: parsePath(t, "foo.bar[1].hello"), Type: types.BlobValue},
 				{Path: parsePath(t, "foo.a"), Type: types.ArrayValue, IsInferred: true,
 					InferredBy: []document.Path{
 						parsePath(t, "foo.a[1][2]"),
@@ -244,24 +244,33 @@ func TestCreateTable(t *testing.T) {
 
 			tb, err := db.Catalog.GetTable(tx, "test")
 			assert.NoError(t, err)
-			require.Len(t, tb.Info.FieldConstraints, 3)
+			require.Len(t, tb.Info.FieldConstraints, 2)
+			require.Len(t, tb.Info.TableConstraints, 3)
 
 			require.Equal(t, &database.FieldConstraint{
-				Path:     parsePath(t, "a"),
-				Type:     types.IntegerValue,
-				IsUnique: true,
+				Path: parsePath(t, "a"),
+				Type: types.IntegerValue,
 			}, tb.Info.FieldConstraints[0])
 
 			require.Equal(t, &database.FieldConstraint{
-				Path:     parsePath(t, "b"),
-				Type:     types.DoubleValue,
-				IsUnique: true,
+				Path: parsePath(t, "b"),
+				Type: types.DoubleValue,
 			}, tb.Info.FieldConstraints[1])
 
-			require.Equal(t, &database.FieldConstraint{
-				Path:     parsePath(t, "c"),
-				IsUnique: true,
-			}, tb.Info.FieldConstraints[2])
+			require.Equal(t, &database.TableConstraint{
+				Path:   parsePath(t, "a"),
+				Unique: true,
+			}, tb.Info.TableConstraints[0])
+
+			require.Equal(t, &database.TableConstraint{
+				Path:   parsePath(t, "b"),
+				Unique: true,
+			}, tb.Info.TableConstraints[1])
+
+			require.Equal(t, &database.TableConstraint{
+				Path:   parsePath(t, "c"),
+				Unique: true,
+			}, tb.Info.TableConstraints[2])
 
 			idx, err := db.Catalog.GetIndex(tx, "test_a_idx")
 			assert.NoError(t, err)
diff --git a/internal/sql/parser/alter_test.go b/internal/sql/parser/alter_test.go
index 48749a5e..2bf1166f 100644
--- a/internal/sql/parser/alter_test.go
+++ b/internal/sql/parser/alter_test.go
@@ -48,28 +48,41 @@ func TestParserAlterTableAddField(t *testing.T) {
 		expected statement.Statement
 		errored  bool
 	}{
-		{"Basic", "ALTER TABLE foo ADD FIELD bar", statement.AlterTableAddField{TableName: "foo",
-			Constraint: database.FieldConstraint{},
-		}, true},
-		{"With type", "ALTER TABLE foo ADD FIELD bar integer", statement.AlterTableAddField{TableName: "foo",
-			Constraint: database.FieldConstraint{
-				Path: document.Path(testutil.ParsePath(t, "bar")),
-				Type: types.IntegerValue,
+		{"Basic", "ALTER TABLE foo ADD FIELD bar", nil, true},
+		{"With type", "ALTER TABLE foo ADD FIELD bar integer", statement.AlterTableAddField{
+			Info: database.TableInfo{
+				TableName: "foo",
+				FieldConstraints: []*database.FieldConstraint{
+					{
+						Path: document.Path(testutil.ParsePath(t, "bar")),
+						Type: types.IntegerValue,
+					},
+				},
 			},
 		}, false},
-		{"With not null", "ALTER TABLE foo ADD FIELD bar NOT NULL", statement.AlterTableAddField{TableName: "foo",
-			Constraint: database.FieldConstraint{
-				Path:      document.Path(testutil.ParsePath(t, "bar")),
-				IsNotNull: true,
+		{"With not null", "ALTER TABLE foo ADD FIELD bar NOT NULL", statement.AlterTableAddField{
+			Info: database.TableInfo{
+				TableName: "foo",
+				FieldConstraints: []*database.FieldConstraint{
+					{
+						Path:      document.Path(testutil.ParsePath(t, "bar")),
+						IsNotNull: true,
+					},
+				},
 			},
 		}, false},
-		{"With primary key", "ALTER TABLE foo ADD FIELD bar PRIMARY KEY", statement.AlterTableAddField{}, true},
-		{"With multiple constraints", "ALTER TABLE foo ADD FIELD bar integer NOT NULL DEFAULT 0", statement.AlterTableAddField{TableName: "foo",
-			Constraint: database.FieldConstraint{
-				Path:         document.Path(testutil.ParsePath(t, "bar")),
-				Type:         types.IntegerValue,
-				IsNotNull:    true,
-				DefaultValue: expr.Constraint(expr.LiteralValue{Value: types.NewIntegerValue(0)}),
+		{"With primary key", "ALTER TABLE foo ADD FIELD bar PRIMARY KEY", nil, true},
+		{"With multiple constraints", "ALTER TABLE foo ADD FIELD bar integer NOT NULL DEFAULT 0", statement.AlterTableAddField{
+			Info: database.TableInfo{
+				TableName: "foo",
+				FieldConstraints: []*database.FieldConstraint{
+					{
+						Path:         document.Path(testutil.ParsePath(t, "bar")),
+						Type:         types.IntegerValue,
+						IsNotNull:    true,
+						DefaultValue: expr.Constraint(expr.LiteralValue{Value: types.NewIntegerValue(0)}),
+					},
+				},
 			},
 		}, false},
 		{"With error / missing FIELD keyword", "ALTER TABLE foo ADD bar", nil, true},
diff --git a/internal/sql/parser/create_test.go b/internal/sql/parser/create_test.go
index f3c5465a..ad2fea2e 100644
--- a/internal/sql/parser/create_test.go
+++ b/internal/sql/parser/create_test.go
@@ -30,7 +30,10 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsPrimaryKey: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), PrimaryKey: true},
 					},
 				},
 			}, false},
@@ -79,14 +82,37 @@ func TestParserCreateTable(t *testing.T) {
 			&statement.CreateTableStmt{
 				Info: database.TableInfo{
 					TableName: "test",
-					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), IsUnique: true},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Unique: true},
 					},
 				},
 			}, false},
 
 		{"With not null twice", "CREATE TABLE test(foo NOT NULL NOT NULL)", nil, true},
-		{"With unique twice", "CREATE TABLE test(foo UNIQUE UNIQUE)", nil, true},
+		{"With unique twice", "CREATE TABLE test(foo UNIQUE UNIQUE)", &statement.CreateTableStmt{
+			Info: database.TableInfo{
+				TableName: "test",
+				TableConstraints: []*database.TableConstraint{
+					{Path: document.Path(testutil.ParsePath(t, "foo")), Unique: true},
+				},
+			},
+		}, false},
+		{"With check", "CREATE TABLE test(a CHECK(a > 10), b int CHECK(a > 20) CHECK(b > 10), CHECK(a > 30))",
+			&statement.CreateTableStmt{
+				Info: database.TableInfo{
+					TableName: "test",
+					FieldConstraints: []*database.FieldConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "b")), Type: types.IntegerValue},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Name: "test_check", Check: expr.Constraint(testutil.ParseExpr(t, "a > 10"))},
+						{Name: "test_check1", Check: expr.Constraint(testutil.ParseExpr(t, "a > 20"))},
+						{Name: "test_check2", Check: expr.Constraint(testutil.ParseExpr(t, "b > 10"))},
+						{Name: "test_check3", Check: expr.Constraint(testutil.ParseExpr(t, "a > 30"))},
+					},
+				},
+			},
+			false},
 		{"With type and not null", "CREATE TABLE test(foo INTEGER NOT NULL)",
 			&statement.CreateTableStmt{
 				Info: database.TableInfo{
@@ -101,7 +127,10 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsPrimaryKey: true, IsNotNull: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsNotNull: true},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), PrimaryKey: true},
 					},
 				},
 			}, false},
@@ -110,7 +139,10 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsPrimaryKey: true, IsNotNull: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsNotNull: true},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), PrimaryKey: true},
 					},
 				},
 			}, false},
@@ -119,10 +151,13 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsPrimaryKey: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
 						{Path: document.Path(testutil.ParsePath(t, "bar")), Type: types.IntegerValue, IsNotNull: true},
 						{Path: document.Path(testutil.ParsePath(t, "baz[4][1].bat")), Type: types.TextValue},
 					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), PrimaryKey: true},
+					},
 				},
 			}, false},
 		{"With table constraints / PK on defined field", "CREATE TABLE test(foo INTEGER, bar NOT NULL, PRIMARY KEY (foo))",
@@ -130,9 +165,12 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsPrimaryKey: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
 						{Path: document.Path(testutil.ParsePath(t, "bar")), IsNotNull: true},
 					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), PrimaryKey: true},
+					},
 				},
 			}, false},
 		{"With table constraints / PK on undefined field", "CREATE TABLE test(foo INTEGER, PRIMARY KEY (bar))",
@@ -141,7 +179,9 @@ func TestParserCreateTable(t *testing.T) {
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
 						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
-						{Path: document.Path(testutil.ParsePath(t, "bar")), IsPrimaryKey: true},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "bar")), PrimaryKey: true},
 					},
 				},
 			}, false},
@@ -153,9 +193,12 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsUnique: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
 						{Path: document.Path(testutil.ParsePath(t, "bar")), IsNotNull: true},
 					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Unique: true},
+					},
 				},
 			}, false},
 		{"With table constraints / UNIQUE on undefined field", "CREATE TABLE test(foo INTEGER, UNIQUE (bar))",
@@ -164,7 +207,9 @@ func TestParserCreateTable(t *testing.T) {
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
 						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
-						{Path: document.Path(testutil.ParsePath(t, "bar")), IsUnique: true},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "bar")), Unique: true},
 					},
 				},
 			}, false},
@@ -173,7 +218,10 @@ func TestParserCreateTable(t *testing.T) {
 				Info: database.TableInfo{
 					TableName: "test",
 					FieldConstraints: []*database.FieldConstraint{
-						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue, IsUnique: true},
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Type: types.IntegerValue},
+					},
+					TableConstraints: []*database.TableConstraint{
+						{Path: document.Path(testutil.ParsePath(t, "foo")), Unique: true},
 					},
 				},
 			}, false},
diff --git a/internal/sql/scanner/scanner_test.go b/internal/sql/scanner/scanner_test.go
index bf617679..f28005fd 100644
--- a/internal/sql/scanner/scanner_test.go
+++ b/internal/sql/scanner/scanner_test.go
@@ -128,6 +128,7 @@ func TestScanner_Scan(t *testing.T) {
 		{s: `BETWEEN`, tok: BETWEEN},
 		{s: `CACHE`, tok: CACHE},
 		{s: `CAST`, tok: CAST},
+		{s: `CHECK`, tok: CHECK},
 		{s: `COMMIT`, tok: COMMIT},
 		{s: `CONFLICT`, tok: CONFLICT},
 		{s: `CREATE`, tok: CREATE},
diff --git a/open_test.go b/open_test.go
index 72f11819..213143c1 100644
--- a/open_test.go
+++ b/open_test.go
@@ -49,7 +49,7 @@ func TestOpen(t *testing.T) {
 	err = res1.Iterate(func(d types.Document) error {
 		count++
 		if count == 1 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT PRIMARY KEY, seq INTEGER)", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"__genji_sequence", "sql":"CREATE TABLE __genji_sequence (name TEXT, seq INTEGER, PRIMARY KEY (name))", "store_name":"X19nZW5qaV9zZXF1ZW5jZQ==", "type":"table"}`)
 			return nil
 		}
 
@@ -64,7 +64,7 @@ func TestOpen(t *testing.T) {
 		}
 
 		if count == 4 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"tableA", "sql":"CREATE TABLE tableA (a INTEGER NOT NULL UNIQUE, b.c[0].d DOUBLE PRIMARY KEY)", "store_name":"AQ==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableA", "sql":"CREATE TABLE tableA (a INTEGER NOT NULL, b.c[0].d DOUBLE, UNIQUE (a), PRIMARY KEY (b.c[0].d))", "store_name":"AQ==", "type":"table"}`)
 			return nil
 		}
 
@@ -74,7 +74,7 @@ func TestOpen(t *testing.T) {
 		}
 
 		if count == 6 {
-			testutil.RequireDocJSONEq(t, d, `{"name":"tableB", "sql":"CREATE TABLE tableB (a TEXT NOT NULL PRIMARY KEY DEFAULT \"hello\")", "store_name":"Aw==", "type":"table"}`)
+			testutil.RequireDocJSONEq(t, d, `{"name":"tableB", "sql":"CREATE TABLE tableB (a TEXT NOT NULL DEFAULT \"hello\", PRIMARY KEY (a))", "store_name":"Aw==", "type":"table"}`)
 			return nil
 		}
 
diff --git a/sqltests/ALTER_TABLE/base.sql b/sqltests/ALTER_TABLE/base.sql
index bc701967..1c5b7fbc 100644
--- a/sqltests/ALTER_TABLE/base.sql
+++ b/sqltests/ALTER_TABLE/base.sql
@@ -7,7 +7,7 @@ SELECT name, sql FROM __genji_catalog WHERE type = "table" AND (name = "test2" O
 /* result:
 {
   "name": "test2",
-  "sql": "CREATE TABLE test2 (a INTEGER PRIMARY KEY)"
+  "sql": "CREATE TABLE test2 (a INTEGER, PRIMARY KEY (a))"
 }
 */
 
diff --git a/sqltests/CREATE_TABLE/check.sql b/sqltests/CREATE_TABLE/check.sql
new file mode 100644
index 00000000..3d82f3be
--- /dev/null
+++ b/sqltests/CREATE_TABLE/check.sql
@@ -0,0 +1,73 @@
+-- test: as field constraint
+CREATE TABLE test (
+    a CHECK(a > 10) CHECK(b < 10)
+);
+SELECT name, type, sql FROM __genji_catalog WHERE name = "test";
+/* result:
+{
+  name: "test",
+  type: "table",
+  sql: "CREATE TABLE test (CHECK (a > 10), CHECK (b < 10))"
+}
+*/
+
+-- test: as field constraint, with other constraints
+CREATE TABLE test (
+    a INT CHECK (a > 10) DEFAULT 100 NOT NULL PRIMARY KEY
+);
+SELECT name, type, sql FROM __genji_catalog WHERE name = "test";
+/* result:
+{
+  name: "test",
+  type: "table",
+  sql: "CREATE TABLE test (a INTEGER NOT NULL DEFAULT 100, CHECK (a > 10), PRIMARY KEY (a))"
+}
+*/
+
+-- test: as field constraint, no parentheses
+CREATE TABLE test (
+    a INT CHECK a > 10
+);
+-- error:
+
+-- test: as field constraint, incompatible default value
+CREATE TABLE test (
+    a INT CHECK (a > 10) DEFAULT 0
+);
+SELECT name, type, sql FROM __genji_catalog WHERE name = "test";
+/* result:
+{
+  name: "test",
+  type: "table",
+  sql: "CREATE TABLE test (a INTEGER DEFAULT 0, CHECK (a > 10))"
+}
+*/
+
+-- test: as field constraint, reference other fields
+CREATE TABLE test (
+    a INT CHECK (a > 10 AND b < 10),
+    b INT
+);
+SELECT name, type, sql FROM __genji_catalog WHERE name = "test";
+/* result:
+{
+  name: "test",
+  type: "table",
+  sql: "CREATE TABLE test (a INTEGER, b INTEGER, CHECK (a > 10 AND b < 10))"
+}
+*/
+
+-- test: as table constraint
+CREATE TABLE test (
+    a INT,
+    CHECK (a > 10),
+    CHECK (a > 20)
+);
+SELECT name, type, sql FROM __genji_catalog WHERE name = "test";
+/* result:
+{
+  name: "test",
+  type: "table",
+  sql: "CREATE TABLE test (a INTEGER, CHECK (a > 10), CHECK (a > 20))"
+}
+*/
diff --git a/sqltests/INSERT/check.sql b/sqltests/INSERT/check.sql
new file mode 100644
index 00000000..e070961b
--- /dev/null
+++ b/sqltests/INSERT/check.sql
@@ -0,0 +1,87 @@
+/*
+Check behavior: These tests check the behavior of the check constraint depending
+on the result of the evaluation of the expression.
+*/
+
+-- test: boolean check constraint
+CREATE TABLE test (a text CHECK(true));
+INSERT INTO test (a) VALUES ("hello");
+/* result:
+{
+    a: "hello"
+}
+*/
+
+-- test: non-boolean check constraint, numeric result
+CREATE TABLE test (a text CHECK(1 + 1));
+INSERT INTO test (a) VALUES ("hello");
+/* result:
+{
+    a: "hello"
+}
+*/
+
+-- test: non-boolean check constraint, non-numeric result
+CREATE TABLE test (a text CHECK("hello"));
+INSERT INTO test (a) VALUES ("hello");
+-- error:
+
+-- test: non-boolean check constraint, NULL
+CREATE TABLE test (a text CHECK(NULL));
+INSERT INTO test (a) VALUES ("hello");
+/* result:
+{
+    a: "hello"
+}
+*/
+
+/*
+Field types: These tests check the behavior of the check constraint depending
+on the type of the field
+*/
+
+-- test: no type constraint, valid double
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+SELECT * FROM test;
+/* result:
+{
+    a: 11.0
+}
+*/
+
+-- test: no type constraint, invalid double
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (1);
+-- error: document violates check constraint "test_check"
+
+-- test: no type constraint, multiple checks, invalid double
+CREATE TABLE test (a CHECK(a > 10), CHECK(a < 20));
+INSERT INTO test (a) VALUES (40);
+-- error: document violates check constraint "test_check1"
+
+-- test: no type constraint, text
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES ("hello");
+-- error: document violates check constraint "test_check"
+
+-- test: no type constraint, null
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (b) VALUES (10);
+SELECT * FROM test;
+/* result:
+{
+    b: 10.0
+}
+*/
+
+-- test: int type constraint, double
+CREATE TABLE test (a int CHECK(a > 10));
+INSERT INTO test (a) VALUES (15.2);
+SELECT * FROM test;
+/* result:
+{
+    a: 15
+}
+*/
+
diff --git a/sqltests/INSERT/constraints.sql b/sqltests/INSERT/types.sql
similarity index 100%
rename from sqltests/INSERT/constraints.sql
rename to sqltests/INSERT/types.sql
diff --git a/sqltests/UPDATE/check.sql b/sqltests/UPDATE/check.sql
new file mode 100644
index 00000000..328ba4fa
--- /dev/null
+++ b/sqltests/UPDATE/check.sql
@@ -0,0 +1,42 @@
+-- test: no type constraint, valid double
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+UPDATE test SET a = 12;
+SELECT * FROM test;
+/* result:
+{
+    a: 12.0
+}
+*/
+
+-- test: no type constraint, invalid double
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+UPDATE test SET a = 1;
+-- error: document violates check constraint "test_check"
+
+-- test: no type constraint, text
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+UPDATE test SET a = "hello";
+-- error: document violates check constraint "test_check"
+
+-- test: no type constraint, null
+CREATE TABLE test (a CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+UPDATE test UNSET a;
+SELECT * FROM test;
+/* result:
+{}
+*/
+
+-- test: int type constraint, double
+CREATE TABLE test (a int CHECK(a > 10));
+INSERT INTO test (a) VALUES (11);
+UPDATE test SET a = 15.2;
+SELECT * FROM test;
+/* result:
+{
+    a: 15
+}
+*/
diff --git a/sqltests/sql_test.go b/sqltests/sql_test.go
index 55650c08..ddc4c21b 100644
--- a/sqltests/sql_test.go
+++ b/sqltests/sql_test.go
@@ -133,6 +133,7 @@ func parse(r io.Reader, filename string) *testSuite {
 	var readingResult bool
 	var readingSetup bool
 	var readingSuite bool
+	var readingCommentBlock bool
 	var suiteIndex int = -1
 
 	var lineCount = 0
@@ -147,7 +148,11 @@ func parse(r io.Reader, filename string) *testSuite {
 
 		switch {
 		case line == "":
-			// ignore blank lines
+		// ignore blank lines
+		case readingCommentBlock && strings.TrimSpace(line) == "*/":
+			readingCommentBlock = false
+		case readingCommentBlock:
+			// ignore comment blocks
 		case strings.HasPrefix(line, "-- setup:"):
 			readingSetup = true
 		case strings.HasPrefix(line, "-- suite:"):
@@ -191,9 +196,11 @@ func parse(r io.Reader, filename string) *testSuite {
 				curTest.Fails = true
 			}
 			curTest = nil
-
-		case strings.HasPrefix(line, "--"): // ignore normal comments
-
+		case strings.HasPrefix(line, "/*"): // ignore block comments
+			readingCommentBlock = true
+		case strings.HasPrefix(line, "--"):
+			// ignore line comments
+		case !readingResult && strings.TrimSpace(line) == "*/":
 		default:
 			if readingSuite {
 				ts.Suites[suiteIndex].PostSetup += line + "\n"