Column Constraints?

[officer-rosmarino]

How can I express constraints? For instance, I need to express that the value of column rating is between >0 and <=5? I see no documentation. Is this possible?

[phuongduyphan]

Hi @officer-rosmarino ,

Currently we haven't supported to define column constraints as in your case. For now, I think you can work-around by using column note syntax to express the constraints like this:

Table movies {
  id int [pk]
  rating int [note: '>0 && <=5']
}

[estan]

This is something I would need as well.

I also need to express table constraints, for example, to constrain the value in one column to be greater than the value in another column. Should I file a separate issue for table constraints?

[djpeach]

So it seems there are two constraints that are currently not possible in DBML. That would be the CHECK constraint, discussed here, and a composite UNIQUE constraint with multiple columns. The composite UNIQUE constraint can be done with a unique index, and is functionally the same thing, but there are cases where the semantics of the Table definitions would be better served with a composite unique constraint instead.

Off the top of my head a good syntax would be something like

Table Foo {
  ...

  constraints {
    <constraint_type> [options]
  }
}

This does break convention with indexes, in that you usually do (columns) [options]. But holds with what the docs say: curly brackets {}: grouping for indexes, constraints and table definitions.

So for unique constraints:

Table Foo {
  ...

  constraints {
    unique [name: 'UC_Col1_Col2', columns: (Col1, Col2)]
  }
}

And for check constraints:

Table Foo {
  ...

  constraints {
    unique [name: 'CC_Age_GTE_21', expression: 'Age >= 21']
  }
}

@officer-rosmarino would this do what you need, and @phuongduyphan would this be feasible and play well with what currently exists? If so, I would be willing to dig into this and attempt these two constraint types for the currently supported SQL database types.