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

sql transactions ddl create-table
joeforker picture joeforker · Jan 14, 2011 · Viewed 75.3k times · Source

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.

Answer

joeforker picture joeforker · Jan 19, 2011

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

Related questions

Do DDL statements always give you an implicit commit, or can you get an implicit rollback?

If you're halfway through a transaction and perform a DDL statement, such as truncating a table, then the transaction commits. I was wondering whether this was always the case and by definition, or is there a setting hidden somewhere that …

Read More
Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, PostgreSQL at least) could also be interesting. I've been doing some "CREATE TABLE" …

Read More
Is it possible to wrap DDL changes in a transaction in PostgreSQL?

I know that in MySQL ddl statements such as alter table/create table/etc cause an implicit transaction commit. As we are moving to PostgreSQL is it possible to wrap multiple DDL statments in a transaction? This would make migration …

Read More