CREATE DATABASE, DROP and TRUNCATE in SQL

Problem

The warehouse wants its own database

The shop opens a warehouse. The warehouse has its own tables: shelves, deliveries, stock counts. They are not part of the website, and nobody wants a mistake in the warehouse to stop the orders. So the warehouse gets its own separate database, on the same server.

So far you have worked only in the shop's database. Now you will see the whole life of a database: how it is made, how you connect to it, how it is filled and how it is removed.

The idea

One server, many databases

A database server (PostgreSQL, MySQL, SQL Server) holds many databases. Each has its own tables, its own users and permissions. A new database is made with CREATE DATABASE:

-- PostgreSQL
CREATE DATABASE warehouse;
\c warehouse          -- psql connects to the new database

-- MySQL
CREATE DATABASE warehouse;
USE warehouse;        -- the next queries run in it

These commands do not work in the fields here, because the site uses SQLite. SQLite has no server: a database is one file. A new database is a new file: sqlite3 warehouse.db in the terminal makes it if it is not there. That is why SQLite has no CREATE DATABASE.

Which database does the field use now? main is the shop's database:

SQLdatabase: shop

SELECT name FROM pragma_database_list;

The idea

A new database in SQLite: ATTACH

Inside a connection that is already open, SQLite attaches another database with ATTACH. On a computer you write a file name (ATTACH 'warehouse.db' AS warehouse) and the file is created if it is not there. In the browser we use ':memory:': a new empty database in memory that disappears with the field. The name after AS is the name of the database, and its tables are written with it in front:

SQLdatabase: shop

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.shelves (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL,
  product_id INTEGER NOT NULL
);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT name FROM pragma_database_list;

The table shelves is in the database warehouse, not in the shop. How exactly the columns are written in CREATE TABLE is in the next lesson. What matters here is where they go.

See it

A query across two databases

In SQLite and MySQL one query can read from two databases at once. The shelves are in the warehouse, and the names and stock are in the shop:

SQLdatabase: shop

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT NOT NULL, product_id INTEGER NOT NULL);
INSERT INTO warehouse.shelves (code, product_id) VALUES ('A1', 7), ('A2', 9);

SELECT s.code, p.name, p.stock
FROM warehouse.shelves s
JOIN main.products p ON p.id = s.product_id;

main.products is the shop's table. main. can be left out: for a table without a database name SQLite looks in the temporary database, then in main and only then in the attached ones. In PostgreSQL this does not work: a query sees only the database it is connected to. There separate parts of one system are split into schemas in one database (warehouse.shelves is the schema warehouse), and separate databases are truly separate.

Try it

The warehouse database

Make the database warehouse (in memory), in it a table deliveries with the columns id (primary key), product_id and quantity (whole numbers, required) and record a delivery: 40 pieces of product 12.

SQLdatabase: shop

ATTACH ':memory:' AS warehouse;

CREATE TABLE warehouse.deliveries (
  id INTEGER PRIMARY KEY
);

Solution

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.deliveries (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL
);
INSERT INTO warehouse.deliveries (product_id, quantity) VALUES (12, 40);

The idea

Four ways to remove

From the smallest to the largest:

DELETE FROM t WHERE … removes the chosen rows. Without WHERE it removes all of them, but the table stays.
TRUNCATE TABLE t (PostgreSQL, MySQL, SQL Server) removes all rows at once, much faster than DELETE on a large table. In MySQL and SQL Server it also resets the numbering of id, and in PostgreSQL only with TRUNCATE TABLE t RESTART IDENTITY. SQLite has no TRUNCATE: there DELETE FROM t without WHERE does the same in a fast way.
DROP TABLE t removes the table itself: the rows, the columns, its indexes. After that t is gone.
DROP DATABASE warehouse removes the whole database with all its tables. In SQLite this is deleting the file.

Here DELETE empties the promotions table, and DROP TABLE removes it. The last query has nothing left to read:

SQLdatabase: shop

CREATE TABLE promo_import (code TEXT, discount INTEGER);
INSERT INTO promo_import VALUES ('AUTUMN10', 10), ('BREAD5', 5);

DELETE FROM promo_import;
SELECT COUNT(*) AS rows_left FROM promo_import;

DROP TABLE promo_import;
SELECT COUNT(*) FROM promo_import;

More precisely, if you are curious

DROP does not ask "are you sure" and does not come back with Ctrl+Z. In PostgreSQL DROP DATABASE does not work while someone is connected to the database, and in PostgreSQL and SQLite a DROP TABLE inside a transaction can be undone with ROLLBACK. In MySQL DROP and TRUNCATE do a COMMIT on their own. That is why before a DROP on a real database you first make a copy (that is what the lesson about the real site in the module "The database in a program" is for).

Try it

A table from an old import

The table promo_import is left over from an import of promotions and is no longer needed. Remove it together with the data, so that the script does not stop if someone has already removed it.

SQLdatabase: shop

SELECT name FROM sqlite_master WHERE type = 'table';

Solution

DROP TABLE IF EXISTS promo_import;

Exercise

Empty it, but keep it

The table cart_log records what customers put in the cart. At the start of every month it is emptied, but it stays for the new records. Empty it.

SQLdatabase: shop

SELECT * FROM cart_log;

Solution

DELETE FROM cart_log;

The idea

Detaching

When you no longer need it, the database is detached with DETACH. The file stays on disk, and its tables are no longer visible from this connection:

SQLdatabase: shop

ATTACH ':memory:' AS warehouse;
CREATE TABLE warehouse.shelves (id INTEGER PRIMARY KEY, code TEXT);
DETACH warehouse;

SELECT name FROM pragma_database_list;

The whole path of a database is: creating (CREATE DATABASE, in SQLite a new file or ATTACH), connecting (\c, USE, ATTACH), tables (CREATE TABLE, the next lesson), data (INSERT, UPDATE, DELETE), queries (SELECT) and finally removing (DROP TABLE, DROP DATABASE). The project at the end of the course goes the whole path with the shop.

Test

Check yourself

1. In PostgreSQL you are connected to the database shop. How do you get to the tables of the database warehouse?SELECT * FROM warehouse.shelvesUSE warehouse;You open a new connection to warehouse (in psql with \c warehouse)

2. Which one removes the rows of a table but leaves it empty and ready for new ones?DELETE without WHERE (or TRUNCATE in PostgreSQL and MySQL)DROP DATABASEDROP TABLE

3. An update script runs DROP TABLE promo_import, but the table is not there. What happens?NothingThe table is made emptyThe script stops with an error; DROP TABLE IF EXISTS would not stop

4. What is a database in SQLite?A process on a serverOne file (or a database in memory)A folder with one file per table

The database: from creating it to DROP

Cookies