Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 79 ยท SQL for Database Courses

DDL: Creating and Altering Schemas

Use SQL DDL the way database courses teach it: CREATE, ALTER and DROP, data types and SQLite type affinity, and constraints that turn an ER design into a schema.

The Data Definition Language (DDL) is the part of SQL that describes the shape of a database rather than its contents: which tables exist, which columns they have, what types those columns hold and which rules the data must obey. In the three-schema view, DDL is how you write the conceptual schema down. The three commands you need are CREATE, ALTER and DROP.

This lesson looks at DDL from the design side: how an ER design becomes a schema, how types really behave (SQLite is unusual here), and how constraints put the rules into the database instead of into application code. For the full syntax of each statement, see CREATE TABLE, ALTER and DROP and Constraints in the SQL tutorial.

Three verbs, one job

CREATE Define a new table, index or view.
ALTER Change an existing definition.
DROP Remove it, data included.

DDL changes the catalog, not just rows.

1 / 2

From an ER model to CREATE TABLE

Take a small ER design: a Department has many Employees, an Employee works on many Projects, and the hours worked belong to the relationship. By the mapping rules, that gives three entity tables and one table for the many-to-many relationship. Order matters: a table can only reference tables that already exist, so create the "one" side first.

SQL A schema from an ER design

Press Run (or Ctrl + Enter) to see the result here.

Notice how much of the ER design survived into the schema: the composite primary key of works_on is the pair of participants, the foreign keys are the relationship lines, NOT NULL records total participation and ON DELETE CASCADE says a work record cannot outlive its employee or project.

Types and SQLite type affinity

Server databases such as PostgreSQL or Oracle enforce column types strictly: put 'hello' into an INTEGER column and the statement fails. SQLite, which runs in this course, works differently. It gives each column a type affinity, a preference rather than a rule:

Declared type contains Affinity Behaviour
INT INTEGER Text that looks like an integer is converted
CHAR, CLOB, TEXT TEXT Numbers are stored as text
REAL, FLOA, DOUB REAL Integers become floating point
BLOB or nothing BLOB (none) Stored exactly as given
anything else (such as NUMERIC, DATE) NUMERIC Numeric-looking text is converted

A value that cannot be converted is stored as it is, so a text value can end up in an INTEGER column. The function typeof() shows what was actually stored. Run this to see it, and then see how a CHECK constraint brings strictness back.

SQL Affinity in action, and a CHECK that fights it

Press Run (or Ctrl + Enter) to see the result here.

SQLite also offers STRICT tables (CREATE TABLE ... (...) STRICT) that enforce the declared types. Whether your site's build supports them depends on the SQLite version, so the lesson uses CHECK instead. The portable lesson: in other engines, choose the narrowest type that fits; in SQLite, add CHECK constraints if the type matters.

Constraints in table design

Constraints are your first line of defence against bad data, and they are better than checks in application code because every program that touches the database has to obey them. Decide them while designing, not afterwards:

  • Primary key on every table. See Keys and Integrity Constraints.
  • NOT NULL for every attribute with total participation or that is simply required.
  • UNIQUE for alternate keys (an email address, a code).
  • FOREIGN KEY for every relationship, with a deliberate ON DELETE action: RESTRICT (refuse), CASCADE (delete dependents) or SET NULL.
  • CHECK for domain rules (positive salary, valid status values).
  • DEFAULT for sensible starting values.

SQLite does not enforce foreign keys unless you run PRAGMA foreign_keys = ON for each connection, which is why the first example begins with it. Try breaking a rule on purpose to see the schema defend itself.

ALTER and DROP

Schemas evolve. ALTER TABLE adds or renames columns and tables, and DROP TABLE removes a table together with its rows. SQLite's ALTER TABLE is limited: it can rename tables and columns, add columns and drop columns (modern versions), but it cannot change a column's type or add a constraint to an existing column. For that you create a new table, copy the data and drop the old one. Server engines support much more, such as ALTER COLUMN ... TYPE. Read the migrations lesson before changing a production table.

SQL Evolving a schema

Press Run (or Ctrl + Enter) to see the result here.

After DROP TABLE the count is 0: the table and its data are gone. Unlike DELETE, a drop cannot be rolled back in many engines, and it fails if other tables still reference the table (when foreign keys are enforced).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why must the department table be created before the employee table that references it?

    The REFERENCES clause points to an existing table, so create the one side first (or add the foreign key later with a separate step where the engine allows it).
  2. 2. In SQLite, what happens if you insert the text 'abc' into an INTEGER column?

    Affinity is a preference. Convertible text becomes a number; anything else is stored as it is. Add a CHECK constraint if you need strict types.
  3. 3. What does ON DELETE CASCADE do on a foreign key?

    CASCADE propagates the delete to referencing rows. It suits weak entities and link tables, where a child cannot exist without its parent.
  4. 4. Which statement removes a table and all its data?

    DROP TABLE deletes the definition and the data. DELETE FROM only removes rows and keeps the table.

Summary

  • DDL (CREATE, ALTER, DROP) writes the schema down; an ER design maps to tables, keys and foreign keys one to one.
  • Create referenced tables first; use composite primary keys for link tables.
  • SQLite uses type affinity, not strict types: inspect with typeof() and use CHECK (or STRICT tables) when types matter.
  • Put rules into constraints: NOT NULL, UNIQUE, CHECK, foreign keys with a chosen ON DELETE action.
  • Enable PRAGMA foreign_keys = ON in SQLite; its ALTER TABLE is limited, so big changes mean create, copy and drop.

Next: Queries Through the Lens of Theory, where SELECT meets relational algebra.