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

by

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

More Related Contents:

  1. Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
  2. PostgreSQL create table if not exists
  3. What are DDL and DML?
  4. SQL Server – transactions roll back on error?
  5. Safely rename tables using serial primary key columns
  6. Delete column from SQLite table
  7. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  8. What happens to an uncommitted transaction when the connection is closed?
  9. Correct use of transactions in SQL Server
  10. MySQL: Transactions vs Locking Tables
  11. Postgres manually alter sequence
  12. How do I add a foreign key to an existing SQLite table?
  13. What does a transaction around a single statement do?
  14. Do database transactions prevent race conditions?
  15. DROP FUNCTION without knowing the number/type of parameters?
  16. Continuing a transaction after primary key violation error
  17. Does Postgres support nested or autonomous transactions?
  18. ‘Lost update’ vs ‘Write skew’
  19. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  20. Should I commit or rollback a read transaction?
  21. Can a COMMIT statement (in SQL) ever fail? How?
  22. Drop column from SQLite table
  23. ALTER TABLE without locking the table?
  24. Can I use CASE statement in a JOIN condition?
  25. How do I write a full outer join query in access
  26. How to run a SQL query on an Excel table?
  27. Transfer data from one database to another database
  28. How to update and order by using ms sql
  29. Difference between FULL JOIN & INNER JOIN
  30. Update multiple rows with one query?

Leave a Comment