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.
Continue readingIn 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.
Continue readingHere are five ways to check whether or not a table exists in a MySQL database.
Continue readingIn SQL, you can use the ALTER TABLE statement to add one or more columns to an existing table.
To do this, use the ADD clause, followed by the column definition. Separate multiple columns with a comma.
Only specify ADD once (i.e. there’s no need to specify it for each column).
In SQL, if you want to remove a table from a database, you need to use the DROP TABLE statement.
That destroys the table and all its data.
Continue readingIn SQLite, you can drop a table with the DROP TABLE statement.
You can optionally add the IF EXISTS clause to suppress any errors that might occur if the table doesn’t exist.
Also, if the table is referenced by a foreign key, there are a few things to be aware of.
Continue readingIn SQL, the ALTER TABLE statement modifies the definition of an existing table.
You can use ALTER TABLE to alter, add, or drop columns and constraints.
Depending on your DBMS, the ALTER TABLE statement can also be used to reassign and rebuild partitions, or disable and enable constraints and triggers.
SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.
Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup/s that will hold each partition. You also need to create a partition function and a partition scheme.
So it goes like this:
Below is an example of using these steps to create a table with four partitions.
Continue readingSometimes you just want to see the basic structure of a table.
In SQLite, there are several ways to retrieve information about a given table. In particular, here are four options:
PRAGMA table_info() statementPRAGMA table_xinfo() statement (for virtual tables).schema commandsqlite_master tableExamples of each method are below.
Continue readingSQLite supports a very limited subset of the ALTER TABLE statement. The only things you can do with ALTER TABLE in SQLite is rename a table, rename a column within a table, or add a new column to an existing table.
In other words, you can’t use ALTER TABLE to add a foreign key to an existing table like you can in other database management systems.
Therefore, the only way you can “add” a foreign key to an existing table in SQLite is to create a new table with a foreign key, then transfer the data to the new table.
There’s more than one way to do this, but there is a recommended way.
Continue reading