Search code examples
sqltransactionsddlcreate-table

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

I am working on a program that issues DDL. I would like to know whether CREATE TABLE and similar DDL can be rolled back in

  • Postgres
  • MySQL
  • SQLite
  • et al

Describe how each database handles transactions with DDL.

Solution

  • http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL's perspective.

    Is DDL transactional according to this document?

    • PostgreSQL - yes
    • MySQL - no; DDL causes an implicit commit
    • Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
    • Older versions of Oracle - no; DDL causes an implicit commit
    • SQL Server - yes
    • Sybase Adaptive Server - yes
    • DB2 - yes
    • Informix - yes
    • Firebird (Interbase) - yes

    SQLite also appears to have transactional DDL as well. I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.