Tables, Rows and Columns
Relation, tuple, attribute and domain are just table, row, column and data type — one model, two vocabularies.
CREATE TABLE tbl_name (col_name data_type [NOT NULL] [DEFAULT value], ...) ENGINE=InnoDB;A table is a named collection of rows, and every row holds one value per column. The formal vocabulary of the relational model maps onto the SQL words one to one, so a paper that talks about tuples and a MySQL manual that talks about rows are describing the same thing.
| Relational model | SQL and MySQL |
|---|---|
| relation | table |
| tuple | row |
| attribute | column |
| domain | data type |
| degree | number of columns |
| cardinality | number of rows |
Where tables live
In MySQL a table belongs to a database, and CREATE SCHEMA is a synonym for CREATE DATABASE — unlike Oracle or PostgreSQL, the two words mean exactly the same thing here. A database is implemented as a directory under the data directory, and a storage engine, InnoDB by default in 8.4, owns the files inside it.
What a table is not
A table has no row order. Rows come back in whatever order the chosen plan produced them, which is why ORDER BY exists. A table also has no built-in identity: the relational model forbids duplicate tuples, MySQL does not, and two byte-identical rows can happily coexist in a table with no PRIMARY KEY and no UNIQUE index.
Example
CREATE TABLE customers (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- surrogate key
email VARCHAR(255) NOT NULL, -- domain: text, <= 255 chars
display_name VARCHAR(100) NULL, -- NULL means unknown, not ''
signup_source ENUM('web','ios','android','import')
NOT NULL DEFAULT 'web', -- domain as an explicit set
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_customers_email (email) -- no two rows share an email
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;When to use it
- Reading a database paper or a normalization argument written in relational vocabulary.
- Auditing an inherited schema for nullable columns and missing keys.
- Explaining to a teammate why the same query returned rows in a different order today.
More examples
Three spellings of one inspection statement
You will meet all of these in other people's scripts. Knowing they are the same statement means you never have to wonder whether DESC on a table is doing something different from SHOW COLUMNS.
SHOW COLUMNS FROM customers; -- the longhand
DESCRIBE customers; -- DESCRIBE is a shortcut for SHOW COLUMNS
DESC customers; -- DESC is the short form of DESCRIBE
-- The parser treats DESCRIBE, DESC and EXPLAIN as synonyms, so this works too,
-- even though EXPLAIN is conventionally reserved for query plans.
EXPLAIN customers;Reading the structure as data
Use this when auditing a schema you did not write — finding every nullable column, every TEXT column, every table missing created_at. It is the version of DESCRIBE you can put in a CI check.
-- information_schema exposes the catalog as ordinary tables, so you can filter,
-- join and script against it. SHOW output cannot be queried this way.
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'shop'
AND is_nullable = 'YES'
ORDER BY table_name, ordinal_position;A table with no identity at all
A concrete demonstration of why a primary key is not bureaucracy. Append-only log tables are the usual place this bites, normally the first time someone needs to correct one bad row.
CREATE TABLE page_hits (path VARCHAR(255) NOT NULL) ENGINE=InnoDB; -- no key
INSERT INTO page_hits VALUES ('/pricing'), ('/pricing');
SELECT COUNT(*) FROM page_hits; -- 2: two rows nothing can tell apart
-- There is no WHERE clause that matches one and not the other, so a row count
-- limit is the only handle you have.
DELETE FROM page_hits WHERE path = '/pricing' LIMIT 1;
Discussion