0

Some ORMs (doctrine) do not support default values in database schema definition, because they states it is not portable. But SQL92 supports the DEFAULT value clause, and all the databases supported by the ORM (MySQL, PostgreSQL, SQL Server, Oracle) support it too.

What are the pitfalls of the DEFAULT clause in different vendors/versions of RDBMS?

1
  • 1
    ORMs ususally go for the least common denominator. And if there is only a single DBMS that doesn't support defaults, that's what the "reference" is. Same goes for check constraints or other "modern" SQL features (think recursive queries)
    – user1822
    Sep 23, 2013 at 8:55

1 Answer 1

2

An ORM that is intended to be truly storage-layer agnostic will generally assume the absolute minimum from the data layer (tables, rows, columns of standard types, indexes, PKs, FKs, and that is about it) and will implement everything else like defaults and more complex constraints themselves.

While every relational database I could mention (mssql, oracle, prostgres, sqlite, mysql, ...) supports default values as defined in SQL-92, things vary a lot more once you consider using document oriented DBs and other less traditional arrangements for your storage layer (they might not support them and if they do the operating semantics may not be quite the same).

5
  • But not all DBMS have implemented DEFAULT values with all the details. Postgres for example allows functions in the default expression. MySQL does not, only constants (with the exception of timestamps that can take CURRENT_TIMESTAMP() as default.) Sep 23, 2013 at 10:57
  • I thought that functions as defaults was a common extension to the standard and that SQL92 only specified constants as defaults? It is a long time since I actually looked at the standards documents so I could be misremembering this though. The use of functions makes it even more DB sensitive (the CURRENT_TIMESTAMP() example would be GETDATE() in MSSQL for instance) than just using constant values (or NULL) does. Sep 23, 2013 at 11:07
  • You could be right, I'm not sure what SQL92 specifies about this. Just wanted to mention the differences in implementation. Sep 23, 2013 at 11:09
  • @DavidSpillett, if ORM struggles for portability by supporting absolute minimum of both RDBMS and NoSQL data layers, it will also should drop JOIN support. As I see all RDBMS support constant DEFAULT values, isn't it greatest common divisor? Sep 23, 2013 at 12:40
  • Some simple ORM libraries don't provide JOIN support, and some don't use the underlying JOIN support in the data layer if they do. I can't imagine that being efficient (reimplementing JOIN in a higher layer) but it does seem to be how some operate. Sep 23, 2013 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.