Creating tables

Lesson 19 of 21 · Part 5 · Changing data and tables · about 20 minutes

Every table in the shop was first created by someone: its name, its columns, what type of value each column holds, and what rules it follows. DESCRIBE shows how a table is defined:

1DESCRIBE products;

Each row is one column: Type is its type, Null says whether it can be empty, and PRI under Key marks the primary key. DESCRIBE is MySQL's; other databases differ: \d products in PostgreSQL's command line, PRAGMA table_info(products) in SQLite. In this lesson we create tables of our own.

CREATE TABLE

The shop wants customers to review products. Create a new reviews table: inside the parentheses, one column per line, the column name and then its type, with commas between the columns:

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    product_id INT,4    stars INT,5    comment VARCHAR(100)6);7 8INSERT INTO reviews (id, product_id, stars, comment)9VALUES (1, 5, 5, 'Great sound'),10    (2, 1, 4, 'Nice paper, a bit thin');11 12SELECT * FROM reviews;

A new table starts out empty; the rows you INSERT are its data.

Common types

Every column has to say what type of value it holds. The ones you'll use most:

Prices use DECIMAL rather than another decimal type because it does money sums without rounding errors. The length in VARCHAR(n) is a rule: text that doesn't fit gives an error:

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    comment VARCHAR(10)4);5 6INSERT INTO reviews (id, comment)7VALUES (1, 'Great sound, comfortable for hours, recommended');

ERROR 1406 (22001): Data too long for column 'comment' at row 1

ERROR 1406: the text is longer than 10 characters.

PRIMARY KEY

The primary key is a row's ID number: every row must have one, and no two may be the same (a duplicate is last lesson's ERROR 1062). Almost every table should have a primary key.

A primary key can also be two columns together. The shop's order_items has PRIMARY KEY (order_id, product_id): in one order, each product has just one row.

Making up ids yourself gets tiresome. In MySQL you can add AUTO_INCREMENT to the primary key: leave id out when inserting, and it uses 1, 2, 3… by itself:

1CREATE TABLE reviews (2    id INT AUTO_INCREMENT PRIMARY KEY,3    stars INT4);5 6INSERT INTO reviews (stars) VALUES (5), (3), (4);7 8SELECT * FROM reviews;

AUTO_INCREMENT is MySQL's way; other databases may write SERIAL (PostgreSQL) or GENERATED ALWAYS AS IDENTITY.

NOT NULL and DEFAULT

NOT NULL says the column must have a value; DEFAULT value says which value to use when an insert leaves the column out (without DEFAULT, it's NULL):

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    stars INT NOT NULL,4    comment VARCHAR(100),5    verified INT NOT NULL DEFAULT 06);7 8INSERT INTO reviews (id, stars) VALUES (1, 5);9 10SELECT * FROM reviews;

comment was left out, so it's NULL; verified was left out, so it got its default, 0. And if stars is NULL:

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    stars INT NOT NULL4);5 6INSERT INTO reviews (id, stars) VALUES (1, NULL);

ERROR 1048 (23000): Column 'stars' cannot be null

ERROR 1048: stars can't be empty.

1CREATE TABLE notes (2    id INT PRIMARY KEY,3    body VARCHAR(50),4    pinned INT DEFAULT 05);6INSERT INTO notes (id) VALUES (1);7SELECT body, pinned FROM notes;

FOREIGN KEY

A review's product_id should be a product that really exists. FOREIGN KEY (column) REFERENCES other_table (column) is that rule: the value in this column must be found in the other table.

1CREATE TABLE reviews (2    id INT PRIMARY KEY,3    product_id INT NOT NULL,4    stars INT NOT NULL,5    FOREIGN KEY (product_id) REFERENCES products (id)6);7 8INSERT INTO reviews (id, product_id, stars) VALUES (1, 5, 5);9SELECT * FROM reviews;10 11INSERT INTO reviews (id, product_id, stars) VALUES (2, 99, 4);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`town`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

Product 5 exists, so the first row goes in; there is no product 99, so the second row is refused: ERROR 1452. The shop's orders.customer_id and order_items.product_id are foreign keys like this, which is why removing product 1 in the last lesson gave ERROR 1451: the other way round, a row that is pointed to can't just be removed.

ALTER TABLE: adding a column

A table can be changed after it's created. ALTER TABLE table ADD COLUMN column type adds a column; the rows already there get NULL (or the default) in it:

1ALTER TABLE customers ADD COLUMN phone VARCHAR(20);2 3UPDATE customers SET phone = '555-0101' WHERE id = 1;4 5SELECT id, name, phone FROM customers WHERE id <= 3;

DROP TABLE: removing a table

DROP TABLE table removes the whole table along with its data; query it afterwards and there's "no such table":

1CREATE TABLE reviews (id INT PRIMARY KEY, stars INT);2DROP TABLE reviews;3 4SELECT * FROM reviews;

ERROR 1146 (42S02): Table 'town.reviews' doesn't exist

Unlike DELETE FROM table (which removes every row but keeps the table), DROP TABLE leaves no table at all. A table that a foreign key still points to can't be dropped:

1DROP TABLE customers;

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.

ERROR 3730: the foreign key in orders still points to customers.

Practice

Write the code, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT * FROM coupons2ORDER BY code;

1CREATE TABLE wishlist (2    customer_id INT NOT NULL,3    product_id INT NOT NULL,4    quantity INT NOT NULL DEFAULT 1,5    PRIMARY KEY (customer_id, product_id),6    FOREIGN KEY (customer_id) REFERENCES customers (id),7    FOREIGN KEY (product_id) REFERENCES products (id)8);9 10INSERT INTO wishlist (customer_id, product_id) VALUES (3, 60);11 12SELECT c.name, p.name AS product, w.quantity13FROM wishlist AS w14JOIN customers AS c ON c.id = w.customer_id15JOIN products AS p ON p.id = w.product_id;

1SELECT name, price2FROM products3WHERE price < 134ORDER BY id;

What you learned

• CREATE TABLE table (column type rules, …); creates a table; DESCRIBE table; shows its definition (in MySQL).
• Common types: INT, VARCHAR(n), DECIMAL(m, d) (use it for money), DATE.
• PRIMARY KEY: every row has one, and no two are the same; MySQL's AUTO_INCREMENT numbers rows for you.
• NOT NULL allows no empty values (ERROR 1048); DEFAULT is the value used when an insert leaves the column out.
• FOREIGN KEY … REFERENCES …: the value must be found in the other table (ERROR 1452).
• ALTER TABLE table ADD COLUMN … adds a column; DROP TABLE table removes the table and its data.