2

Which one to use or which one is the better? There's any difference?

searchtype_id bigint NOT NULL
or
CONSTRAINT searchtype_id_nn CHECK ((searchtype_id IS NOT NULL))

Botond Hegyi
  • 81
  • 7
  • 1
    I didn't understand how those two SQL fragments were equivalent – Caius Jard Feb 14 '19 at 11:16
  • 1
    You mean `searchtype_id bigint NOT NULL`. – jarlh Feb 14 '19 at 11:18
  • 3
    There is no difference in how they behave. Out-of-line `CONSTRAINT` can have name [for more detail look here](https://codingsight.com/difference-between-inline-and-out-of-line-constraints/) – karthickj25 Feb 14 '19 at 11:18
  • The constraint object can be disabled on most databases. To do this on the column-type declaration you will have to change it. – EzLo Feb 14 '19 at 11:30
  • Some DBMS might lie about their implementation of check constraints. In MySQL those two are not the same as it silently ignores the check constraint. –  Feb 14 '19 at 11:32

2 Answers2

3

Is there a difference? Yes. NOT NULL is part of the storage definition of the type of the column. So, NOT NULL can affect how the value is stored (is a NULL-flag required?). A NOT NULL definition can also be used for optimizations during the compilation phase of a query.

By contrast, a CHECK constraint does validate that the data meets certain characteristics, but it is less likely that this information will be used in the compilation phase.

The NOT NULL definition predates the CHECK constraint and is standard across all databases.

NULL-ability is something that I think of as part of the type -- because it is a declaration built into the language that says "this column is required to have a value". An integer column that can take on NULL values is subtly different from an integer that cannot.

I would recommend using the NOT NULL syntax rather than a CHECK constraint. It gives the database more information about the column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Both are different and it's hard to choose between them as some things apply to one while some doesn't to other like NOT NULL constraint can only be declared inline while CHECK constraints can applied to out-of-line constraint. If I had to choose between one of them I'll choose out-of-line as:

  • I'll be able to individually name my constraints which helps in debugging once you get an error or something.
  • Also CHECK constraint will allow me to refer single as well as multiple columns which can only be used as out-of-line constraint.
immukul
  • 87
  • 11