tables

Add a Column to a Table in SQL

by

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).

Read more

SQLite DROP TABLE

by

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.

Read more

SQL ALTER TABLE for Beginners

by

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.

Read more

Create a Partitioned Table in SQL Server (T-SQL)

by

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:

  1. Create filegroup/s
  2. Create a partition function
  3. Create a partition scheme
  4. Create the partitioned table

Below is an example of using these steps to create a table with four partitions.

Read more

Add a Foreign Key to an Existing Table in SQLite

by

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.

Read more

2 Ways to List the Tables in an SQLite Database

by

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.

Read more