How do I CASCADE DROP a table being referenced in MariaDB? If I have a table that references another one

CREATE TABLE foo ( x int PRIMARY KEY );
CREATE TABLE bar (
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x)
);

DROP TABLE foo;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails


In PostgreSQL I can easily use DROP ... CASCADE


  CASCADE Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.13).


Like this,

# CREATE TABLE foo ( x int PRIMARY KEY );
# CREATE TABLE bar ( x int REFERENCES foo );
# DROP TABLE foo CASCADE;
NOTICE:  drop cascades to constraint bar_x_fkey on table bar
DROP TABLE


However, in MySQL, even with CASCADE you get an error,

DROP TABLE foo CASCADE;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
I don't think that's how CASCADE works with MySQL derivatives!

Tables:

CREATE TABLE foo 
( 
  x int PRIMARY KEY 
);

CREATE TABLE bar 
(
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ON DELETE CASCADE
);


Some values:

INSERT INTO foo VALUES (3), (45), (7);
INSERT INTO bar VALUES(3), (3), (45), (7), (7);


Then:

DELETE FROM foo WHERE x = 3;


Now:

SELECT * FROM bar;

 x
 7
 7
45


So, (for the MySQL family) the DRI (Declarative Referential Integrity) works on the level of records and not on a table level. The referential constraint exists on the table bar and not on foo - therefore deleting foo has no effect on that constraint - and the system won't let you delete the parent table, but will let you delete the parent record.

Take a  look at the fiddle here and it's fairly clear (and actually logical enough in one sense) if you reflect on it - at least to me it is! 

However, a big caveat to the logic argument - PostgreSQL also doesn't allow 

DROP TABLE foo;

but with a far more informative error message:

ERROR:  cannot drop table foo because other objects depend on it
DETAIL:  constraint asdf on table bar depends on table foo
HINT:  Use DROP ... CASCADE to drop the dependent objects too


however

DROP TABLE foo CASCADE;


does succeed.

So, MySQL fails on both statements, whereas PostgreSQL's solution is (IMHO and as usual) superior - it "forces" the CASCADE if it's explicitly invoked. MySQL won't let it delete under either circumstance.