My guess would be that a foreign key reference is set to RESTRICT by default. But, is there any standard for this? Is the default equal on any database type? Or should these values be defined in all statements just to be sure?
+1
A:
For postgres NO ACTION is the default, which for most purposes is same as RESTRICT, as stated here.
I would recommend to be explicit, especially in DDL, whenever in doubt (and then some). There are various reasons for this:
- Some behaviour for data definition statements can depend on server settings and versions, so your backup and restore could benefit from being explicit
- If you try to move your data from one RDBMS engine to another being explicit can help you catch misunderstanding between the two dialects (if they will not silently skip over the part they don't get)
- If you even think about ambiguity now, try to imagine the next guy maintaining the database or yourself in a few years - being explicit and commenting in your create scripts will pay off one day
Unreason
2010-03-24 10:32:06
For ISO/ANSI Standard SQL-92 the default is `NO ACTION`. This is because explicit referential actions (including `NO ACTION`) is a full SQL-92 feature. In other words, entry level SQL-92 allows you to create a FK without specifying a referential action and `NO ACTION` is how most practitioners would expect a foreign key to work by default. Also consider that when SQL-92 was introduced many SQL products lacked the `CASCADE` referential action.
onedaywhen
2010-03-25 09:23:27
A:
You should also take into account that not all DBMSs admit these options (nor allow the same values in them). For instance, I think that Oracle does not support the onupdate clause
Jordi Cabot
2010-03-24 16:42:21