Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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) –  a_horse_with_no_name Sep 23 '13 at 8:55

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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.) –  ypercube Sep 23 '13 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. –  David Spillett Sep 23 '13 at 11:07
    
You could be right, I'm not sure what SQL92 specifies about this. Just wanted to mention the differences in implementation. –  ypercube Sep 23 '13 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? –  Apres Ahperes Sep 23 '13 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. –  David Spillett Sep 23 '13 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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