CODEWITHSUNDEEP
sqlforeign-keysconstraints
tigger
tigger

Reputation: 1906

What's the default of ONDELETE and ONUPDATE for foreign keys in SQL?

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?

Upvotes: 3

Views: 201

Answers (2)

Jordi Cabot
Jordi Cabot

Reputation: 8228

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

Upvotes: 0

Unreason
Unreason

Reputation: 12704

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

Upvotes: 1

Related Questions