--- title: 'Creating Tables' source: 'https://academia.sh/en/courses/sql-fundamentals/creating-tables' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:51+00:00' license: 'CC BY-SA 4.0' --- # Creating Tables The parts of a column definition, column-level and table-level constraints, how keys are expressed in a schema, and the error messages a constraint violation produces in the engine. The course so far has been about reading data that already exists: selecting columns, writing conditions, joining tables, grouping rows, and combining result sets with set operations. Every query took an already-built schema as a given. This lesson removes that assumption and asks the question that comes next: how is the schema itself written. The first of the families separated out in the SQL Language Families lesson — data definition language — is this topic's territory. The library schema was set up in that lesson, and three kinds of constraint were introduced there: `PRIMARY KEY`, `NOT NULL`, and `REFERENCES`. That lesson explained what these declarations say; this one shows what they prevent, and adds to the list what lies beyond the three — default values, value-range checks, uniqueness, and named constraints. A table definition sets the rule for which rows may enter that table. If the rule lives in the schema, the engine enforces it on every write attempt; if the rule lives only in application code, every path that bypasses that code punches through it. This lesson's subject is that difference. ## Parts of a Table Definition The body of a `CREATE TABLE` statement is a comma-separated list of items. Each item is either a column definition or a table constraint. A column definition has three parts: a name, a type, and zero or more **constraints**. The smallest table in the schema holds branches. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'); INSERT INTO branch (branch_id, name, city) VALUES (3,'Kadikoy','Istanbul'); SELECT * FROM branch; SQL ``` ``` branch_id name city --------- ------------ -------- 1 Central Ankara 2 Bahcelievler Ankara 3 Kadikoy Istanbul ``` The `:memory:` argument on the command line builds the database in memory rather than on disk: the moment the example ends, it disappears. Every block in this lesson runs this way and leaves no file behind. The `library.db` file from the querying lessons is left untouched. ## Forbidding Blanks and Setting a Default The Data Modeling and Relational Theory course showed that a null value means "unknown" and triggers three-valued logic. When a blank makes no sense for a column, the schema says so: `NOT NULL`. `DEFAULT` specifies what a column is filled with when an insert leaves it unspecified. `UNIQUE` requires that the values in a column never repeat. The member table uses all three. Two things change from the definition used in the querying lessons: email must now be unique, and a column holding membership status has been added. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' ); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (2,'Ben',NULL,'ben@example.test','2023-05-30'); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (3,'Clara','Diaz','alice@example.test','2024-01-09'); SELECT * FROM member; SQL ``` ``` Runtime error near line 14: NOT NULL constraint failed: member.last_name (19) Runtime error near line 16: UNIQUE constraint failed: member.email (19) member_id first_name last_name email registered_at status --------- ---------- --------- ------------------ ------------- ------ 1 Alice Kane alice@example.test 2023-02-14 active ``` Three inserts were attempted, one succeeded. The first row got the value `active` for `status` even though it never wrote to that column: the default kicked in. The second left the last name blank and ran into the `NOT NULL` constraint. The third repeated an email address that was already in use. The structure of the error message matters: which kind of constraint, which table, which column. On a constraint violation the engine does not write the row; the statement fails and its effect is undone in full. There is no such thing as a partially written row. ## Uniqueness and Null Values `PRIMARY KEY` is the schema-level counterpart of the primary key defined in the Relational Theory course, and it is a combination of two constraints: uniqueness plus not being null. `UNIQUE` asks for only the first of the two. A null value is what makes the difference visible. In standard SQL, a unique column can hold more than one null, because two unknown values cannot be said to be equal. The `email` column in the schema is an example: some members have no email on file. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL ); INSERT INTO member VALUES (3,'Clara','Diaz',NULL,'2024-01-09'); INSERT INTO member VALUES (5,'Grace','Kim',NULL,'2024-11-05'); INSERT INTO member VALUES (6,'Owen','Park','owen@example.test','2025-01-18'); SELECT count(*) AS rows, count(email) AS with_email FROM member; SQL ``` ``` rows with_email ---- ---------- 3 1 ``` Two rows stood with a blank email at the same time. This behavior is a point where engines can differ: some accept more than one null, others offer additional syntax that leaves the decision to the designer. When a column needs to be both unique and mandatory, the pair `NOT NULL UNIQUE` is written — which is exactly the definition of a primary key. A key can also be made up of more than one column. The rule that stops a member from putting the same book on hold twice is a composite primary key written at the table level. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE reservation ( member_id INTEGER NOT NULL, book_id INTEGER NOT NULL, date TEXT NOT NULL, PRIMARY KEY (member_id, book_id) ); INSERT INTO reservation VALUES (1, 5, '2025-07-01'); INSERT INTO reservation VALUES (2, 5, '2025-07-02'); INSERT INTO reservation VALUES (1, 6, '2025-07-03'); INSERT INTO reservation VALUES (1, 5, '2025-07-04'); SELECT * FROM reservation; SQL ``` ``` Runtime error near line 13: UNIQUE constraint failed: reservation.member_id, reservation.book_id (19) member_id book_id date --------- ------- ---------- 1 5 2025-07-01 2 5 2025-07-02 1 6 2025-07-03 ``` Different members could hold the same book, and the same member could hold different books; the only thing that repeated was the pair itself. In a composite key, uniqueness applies to the value the columns form together, not to each column on its own. ## Narrowing the Value Range `CHECK` writes a condition that gets validated when a row is written. If the condition evaluates to false, the row is rejected. A rule such as membership status being one of three values gets moved into the schema this way, rather than living in application code. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); INSERT INTO member (member_id, first_name, last_name, registered_at, status) VALUES (1,'Alice','Kane','2023-02-14','suspended'); INSERT INTO member (member_id, first_name, last_name, registered_at, status) VALUES (2,'Ben','Ortiz','2023-05-30','inactive'); SELECT * FROM member; SQL ``` ``` Runtime error near line 14: CHECK constraint failed: status IN ('active', 'suspended', 'closed') (19) member_id first_name last_name registered_at status --------- ---------- --------- ------------- --------- 1 Alice Kane 2023-02-14 suspended ``` A check constraint written inside a column definition can only look at that column. When a rule needs to test two columns together, the constraint is written at the end of the column list as a **table constraint**. The prefix `CONSTRAINT name` gives the constraint a name; that name shows up in the error message and makes it readable which rule the error belongs to. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT, CONSTRAINT return_after_checkout CHECK (return_date IS NULL OR return_date >= pickup_date) ); INSERT INTO loan VALUES (1, 1, 1, '2025-01-10', '2025-01-24'); INSERT INTO loan VALUES (3, 1, 2, '2025-02-11', NULL); INSERT INTO loan VALUES (4, 3, 3, '2025-03-01', '2025-02-15'); SELECT * FROM loan; SQL ``` ``` Runtime error near line 15: CHECK constraint failed: return_after_checkout (19) loan_id book_id member_id pickup_date return_date ------- ------- --------- ----------- ----------- 1 1 1 2025-01-10 2025-01-24 3 1 2 2025-02-11 ``` The `return_date IS NULL OR` part of the condition cannot be left out. A comparison against a null value evaluates to unknown rather than true; if the constraint consisted only of the second condition, loans that had not yet been returned would be rejected too. The three-valued logic of null values is the most common place to stumble when writing a constraint. ## Links Between Tables The `REFERENCES` syntax requires that a value in one column be found among the keys of another table. This is referential integrity's expression in the schema: a book cannot be written against a branch that does not exist. Whether foreign key checking is switched on differs from engine to engine. In the command-line tool used here, the check is off by default and is turned on per connection. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9); SELECT * FROM book; SQL ``` ``` book_id title author publication_year branch_id ------- --------- ------------- ---------------- --------- 1 Blindness José Saramago 1995 9 ``` The constraint written into the schema was not enforced; a row carrying a branch number that does not exist entered the table. When the same block is opened with a `PRAGMA foreign_keys = ON;` line, the result changes. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); INSERT INTO branch VALUES (1,'Central','Ankara'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,9); INSERT INTO book VALUES (2,'The Disconnected','Oğuz Atay',1972,1); INSERT INTO book VALUES (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); SELECT * FROM book; SQL ``` ``` Runtime error near line 15: FOREIGN KEY constraint failed (19) book_id title author publication_year branch_id ------- ----------------- --------- ---------------- --------- 2 The Disconnected Oğuz Atay 1972 1 7 Tehlikeli Oyunlar Oğuz Atay 1973 ``` The book linked to a branch that does not exist was rejected, and the one linked to a valid branch was accepted. The third row's branch number is null, and that one was accepted too: a foreign key does not check when the column is null — "which branch it is in is unknown" is different from "in a branch that does not exist." The `PRAGMA` syntax is not part of standard SQL; it belongs to the engine in use. The lesson that transfers is this: **a constraint being written into the schema does not mean it is being checked.** When starting work with a new database, the state of foreign key checking is tested; a check left off means orphan rows discovered months later. ## The Library Schema Put together, this lesson's pieces form the schema the rest of the topic uses. ```sh sqlite3 :memory: <<'SQL' .headers on .mode column PRAGMA foreign_keys = ON; CREATE TABLE branch ( branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL ); CREATE TABLE book ( book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER CHECK (publication_year BETWEEN 1450 AND 2100), branch_id INTEGER REFERENCES branch(branch_id) ); CREATE TABLE member ( member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE, registered_at TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')) ); CREATE TABLE loan ( loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT, CONSTRAINT return_after_checkout CHECK (return_date IS NULL OR return_date >= pickup_date) ); INSERT INTO branch VALUES (1,'Central','Ankara'),(3,'Kadikoy','Istanbul'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(5,'Silent House','Orhan Pamuk',1983,3); INSERT INTO member (member_id, first_name, last_name, email, registered_at) VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'),(3,'Clara','Diaz',NULL,'2024-01-09'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(4,5,3,'2025-03-01',NULL); SELECT u.first_name, u.last_name, k.title, s.name AS branch, o.pickup_date FROM loan AS o JOIN member AS u ON u.member_id = o.member_id JOIN book AS k ON k.book_id = o.book_id JOIN branch AS s ON s.branch_id = k.branch_id ORDER BY o.loan_id; SQL ``` ``` first_name last_name title branch pickup_date ---------- --------- ------------ ------- ----------- Alice Kane Blindness Central 2025-01-10 Clara Diaz Silent House Kadikoy 2025-03-01 ``` All four tables have a primary key that is an integer not drawn from the data itself. Books had a natural candidate — the ISBN — but that number names an edition; two copies of the same edition carry the same number, and the key cannot tell the copies apart. A natural field that looks like a key but cannot serve as one is a situation that comes up often in schema design, and it is usually resolved with a generated key like the one in this lesson. ## Summary - A column definition consists of a name, a type, and constraints; if a constraint lives in the schema the engine enforces it on every write path, and if it lives in application code it is enforced only on the path that goes through that code. - `NOT NULL` forbids blanks, `DEFAULT` fills in a column that was not written, and `CHECK` narrows the range of values; on a column that can be null, the check condition must handle the blank separately. - `UNIQUE` asks for uniqueness alone, `PRIMARY KEY` asks for uniqueness together with being mandatory; a key can be made up of more than one column, and uniqueness applies to the value those columns produce together. - `REFERENCES` writes referential integrity into the schema, does not check on a null column, and whether the check is switched on differs from engine to engine. - On a constraint violation the statement fails in full; because the error message gives the constraint type, the table, and the constraint name, the diagnosis can be read directly. ## Next Step A schema does not stay in the shape it was first written: a new column becomes necessary, a column's name turns out to have been chosen badly, a constraint needs to be added after the fact. The next lesson takes up altering the schema and shows the real difficulty: the changes `ALTER TABLE` can make in a single statement are limited, and that limit differs between engines. Past the limit, the table gets rebuilt; that lesson also sets up the portable, data-preserving form of that rebuild.