4

SQL Server supports DIE syntax(drop table if exists):

Multiple tables can be dropped in any database. If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

Example:

CREATE TABLE t1(i INT PRIMARY KEY);
CREATE TABLE t2(i INT PRIMARY KEY);
CREATE TABLE t3(i INT PRIMARY KEY, t2_id INT REFERENCES t2(i));

The proper way of droping objects is: t1/t3, and finally t2.

Let's say we run:

DROP TABLE IF EXISTS t1,t2,t3;
-- Could not drop object 't2' because it is referenced by a FOREIGN KEY constraint.

Everything is as expected, the DDL cannot complete successfully because there is FK holding.

Now, I was expecting that for DDL should hold all-or-nothing behaviour. So I should still have my 3 tables intact. But it is not the case, tables t1 and t3 were dropped.

SELECT * FROM t1;
-- Invalid object name 't1'.

SELECT * FROM t2;
SELECT * FROM t3;
-- Invalid object name 't3'.

db<>fiddle demo

It looks that internally it is executed as 3 independent drop statements which is odd.

Is there any reason why tables t1 and t3 are gone or am I missing something obvious?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • From the docs: "DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first." So your code should be *DROP TABLE IF EXISTS t1,t3,t2;* – MJH Jun 16 '19 at 21:41
  • @MJH Yes, I stated that fact `"The proper way of droping objects is: t1/t3, and finally t2"` My question is why the DROP TABLE is not an atomic operation. – Lukasz Szozda Jun 16 '19 at 21:43
  • 1
    Sorry, skimmed through the question. I don't think that syntax is "standard SQL" and is implemented badly, if you add a transaction around the DROP statement then none of the tables are dropped. – MJH Jun 16 '19 at 21:58

1 Answers1

0

The problem is, DBMS will drop any table it can, but this operation is not atomic. If you want an atomic expression, you can use a conditional ROLLBACK into a transaction (see here for more)

crissal
  • 2,547
  • 7
  • 25
  • `but this operation is not atomic` Yes, the behaviour indicates that. Could you provide any official source that states this fact? I am curious which operations do not preserve all-or-nothing rule. – Lukasz Szozda Jun 16 '19 at 21:31
  • It seems like it's just applying drop table to every table you lost (and this fact is sustained by this statement 'If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.') – crissal Jun 16 '19 at 21:36