Here are five ways to check whether or not a table exists in a MySQL database.
Continue readingTag: tables
Add a Column to a Table in SQL
In 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).
SQL DROP TABLE for Beginners
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 readingSQLite DROP TABLE
In 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 readingSQL ALTER TABLE for Beginners
In 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.
Create a Partitioned Table in SQL Server (T-SQL)
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:
- Create filegroup/s
- Create a partition function
- Create a partition scheme
- Create the partitioned table
Below is an example of using these steps to create a table with four partitions.
Continue reading4 Ways to Get Information about a Table’s Structure in SQLite
Sometimes 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:
- The
PRAGMA table_info()statement - The
PRAGMA table_xinfo()statement (for virtual tables) - The
.schemacommand - The
sqlite_mastertable
Examples of each method are below.
Continue readingAdd a Foreign Key to an Existing Table in SQLite
SQLite 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 readingList all Temporary Tables in SQLite
As with most things in SQLite, there’s more than one way to get a list of temporary tables in a database.
Here I present two ways to return temporary tables in SQLite.
Continue reading2 Ways to List the Tables in an SQLite Database
Here are two ways to return a list of tables in all attached databases in SQLite.
The first method returns all tables and views for all attached databases.
The second method gives you the option of returning both tables and views, or just tables, but only for the primary database.
Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article.
Continue reading