Creating table in mysql

Question

Is it possible to create more than one table at a time using single create table statement.

Answer 1

Not with MS SQL Server. Not sure about mysql.

Can you give more info on why you'd want to do this? Perhaps there's an alternative approach.

Answer 2

I don't know, but I don't think you can do that. Why you want to do this?

Answer 3

Not in standard SQL using just the 'CREATE TABLE' statement. However, you can write multiple statements inside a CREATE SCHEMA statement, and some of those statements can be CREATE TABLE statements. Next question - does your DBMS support CREATE SCHEMA? And does it have any untoward side-effects?

Judging from the MySQL manual pages, it does support CREATE SCHEMA as a synonym for CREATE DATABASE. That would be an example of one of the 'untoward side-effects' I was referring to.

(Did you know that standard SQL does not provide a 'CREATE DATABASE' statement?)

Answer 4

I don't think it's possible to create more than one table with a 'CREATE TABLE' command. Everything really depends on what you want to do. If you want the creation to be atomic, transactions are probably the way to go. If you create all your tables inside a transaction, it will act as a single create statement from the perspective of anything going on outside the transaction.

Answer 5

For MySQL, you can use multi-query to execute multiple SQL statements in a single call. You'd issue two CREATE TABLE statements separated by a semicolon.

But each CREATE TABLE statement individually can create only one table. The syntax supported by MySQL does not allow multiple tables to be created simultaneously.

@bsdfish suggests using transactions, but DDL statements like CREATE TABLE cause implicit transaction commits. There's no way to execute multiple CREATE TABLE statements in a single transaction in MySQL.

---

I'm also curious why you would need to create two tables simultaneously. The only idea I could come up with is if the two tables have cyclical dependencies, i.e. they reference each other with foreign keys. The solution to that is to create the first table without that foreign key, then create the second table, then add the foreign key to the first table with ALTER TABLE ADD CONSTRAINT. Dropping either table requires a similar process in reverse.