We can use MariaDB’s SHOW CREATE TABLE statement to generate a CREATE TABLE script for existing tables in the database. This allows us to recreate the table without having to manually type out the table’s definition.
The statement requires SELECT privilege for the table.
Probably the most common way of creating a table in SQL is to use a basic CREATE TABLE statement, along with the table’s definition. But that’s not the only way of doing it.
Below are five options for creating a table when using SQL.
The very useful CREATE TABLE IF NOT EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a CREATE TABLE statement without getting an error if the table already exists.
Earlier versions of Oracle don’t support the IF NOT EXISTS clause, and so if we want to avoid any nasty errors resulting from trying to create a table that already exists, we need to do a bit of extra work.
Dropping a table in SQL easy. You simply use DROP TABLE myTable where myTable is the name of the table you want to drop. You can use this method to drop a table in SQL Server via T-SQL script.
But you’ll get an error if the table doesn’t actually exist. That is, unless you check for the existence of the table first.
Below are four ways of using T-SQL to check whether the table exists before dropping it.
In SQLite, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table or view of the same name already exists in the database before creating it.
Creating a table without this clause would normally result in an error if a table of the same name already existed in the database. But when using the IF NOT EXISTS clause, the statement has no effect if a table already exists with the same name.
In MariaDB, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database before creating it.
The table will only be created if there isn’t already one with the same name.
In PostgreSQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database before creating it.
The table will only be created if no other table exists with the same name. If a table already exists with that name, a “notice” will be issued instead of an error.
In MySQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database.
If the table doesn’t exist, it will be created. If it already exists, it won’t be created.