Today I Learned

Sqlite does not check for foreign key integrity when creating tables, only when inserting records. However, MySQL does check in both cases.

To make this more clear, let's take a look at a simple example: given two tables - todos and users - created in this exact order:

 Schema::create('todos', function (Blueprint $table) {
      $table->id();
      $table->string('title');
      $table->unsignedBigInteger('user_id');
      $table->foreign('user_id')
          ->references('id')
          ->on('users');
  });

 Schema::create('users', function (Blueprint $table) {
      $table->id();
      $table->string('username');
      $table->string('password');
  });

If you run your migrations using the sqlite driver, everything works just fine.

However, if you run your migrations using the mysql driver, you get the following error:

SQLSTATE[HY000]: General error: 1005 Can't create table `[your-app]`.`todos` 
(errno: 150 "Foreign key constraint is incorrectly formed") 
(SQL: alter table `todos` add constraint `todos_user_id_foreign`
 foreign key (`user_id`) references `users` (`id`))

When you have to delete 100+ databases created by a botched deployment script you have a few options available:

1. Plain SQL

You'll have to run this query and then copy paste the result into a second query.

SELECT
	GROUP_CONCAT( CONCAT( 'DROP DATABASE `', SCHEMA_NAME, '`;' ) SEPARATOR ' ' ) AS DELETE_QUERY
FROM
	information_schema.SCHEMATA 
WHERE
	SCHEMA_NAME LIKE '{DBNAME}%'

2. Stored procedure

This allows you to create a stored procedure that will run your query in one go.

CREATE PROCEDURE clean () BEGIN

	SET @QUERY := (
		SELECT
			CONCAT( 'DROP DATABASE `', SCHEMA_NAME, '`;' ) 
		FROM
			`information_schema`.`SCHEMATA` 
		WHERE
			SCHEMA_NAME LIKE '{DBNAME}%' 
			LIMIT 1 
		);
	PREPARE stmt 
	FROM
		@QUERY;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
END;
CALL clean ();
DROP PROCEDURE clean;