Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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

  • Postgres
  • MySQL
  • SQLite
  • et al

Describe how each database handles transactions with DDL.

1 Answer

0 votes
by (40.4k points)

Following databases can handle the transactions with DDL

  • PostgreSQL - yes this can handle the transactions with DDL
  • MySQL - no; Because in this 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; Because in this DDL causes an implicit commit
  • SQL Server - yes this can handle the transactions with DDL
  • Sybase Adaptive Server - yes this can handle the transactions with DDL
  • DB2 - yes this can handle the transactions with DDL
  • Informix - yes this can handle the transactions with DDL
  • Firebird (Interbase) - yes this can handle the transactions with DDL
  • SQLite also appears to have transactional DDL as well. You will be able to ROLLBACK a CREATE TABLE statement in SQLite. 

This CREATE TABLE documentation does not mention any special transactional 'gotchas'.

The below link will provide an overview of this issue from PostgreSQL's perspective.

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 17, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 26, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...