Why is column default value not portable through different RDBMS?

Question

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?

• user1822: 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)

Answer 1

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).

• ypercubeᵀᴹ: 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.)
• David Spillett: 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.
• ypercubeᵀᴹ: You could be right, I'm not sure what SQL92 specifies about this. Just wanted to mention the differences in implementation.
• Apres Ahperes: @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?
• David Spillett: 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.