SQL Database Design: Schema, Tables, Keys and Constraints

SQL beginner course · Chapter 2 of 10

SQL Database Design: Schema, Tables, Keys and Constraints explained in simple steps. Examples use practice tables such as students and scores; adapt names to your database.

Follow the concepts from left to right, then run the examples below.

In this lesson

1. Schemas and databases
2. Create, alter and drop a schema
3. Create and alter a database
4. CREATE TABLE and data types
5. Primary and foreign keys
6. Other constraints and ALTER TABLE
7. DROP and database safety

Schemas and databases

A database stores related objects; a schema groups named objects. CREATE SCHEMA is widely supported, while creating a database and switching schemas varies by product.

Example

CREATE SCHEMA school;

Expected result: A namespace for school objects is created.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Create, alter and drop a schema

CREATE SCHEMA makes a namespace. ALTER SCHEMA is not portable: some systems use it for moving objects, others use ALTER SCHEMA to change ownership. DROP SCHEMA removes the namespace and may require it to be empty. Always check your product documentation.

Example

CREATE SCHEMA school;

Expected result: A school namespace is created.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Create and alter a database

CREATE DATABASE makes a database in systems that support this statement. ALTER DATABASE changes database-level settings, but the options differ greatly by product. SQLite creates a database by opening a file instead.

Example

CREATE DATABASE school_lab;

Expected result: A separate school_lab database is created where supported.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

CREATE TABLE and data types

Pick a type that matches the value: INTEGER for counts, VARCHAR for text, DECIMAL for exact money, and DATE for a date. Size and support differ by database.

Example

CREATE TABLE students (student_id INTEGER PRIMARY KEY, student_name VARCHAR(80) NOT NULL, grade INTEGER);

Expected result: A student table with required identity and name is created.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Primary and foreign keys

A primary key uniquely identifies a row. A foreign key points to a row in another table and protects relationships. A child row should not refer to a missing parent.

Example

CREATE TABLE scores (student_id INTEGER REFERENCES students(student_id), points INTEGER);

Expected result: Each score is attached to an existing student.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Other constraints and ALTER TABLE

NOT NULL requires a value, UNIQUE prevents duplicates, and CHECK limits allowed values. ALTER TABLE changes structure; test the change before applying it to real data.

Example

ALTER TABLE students ADD email VARCHAR(120);

Expected result: An email column is added without deleting existing rows.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

DROP and database safety

DROP removes an object and its data; it is different from DELETE, which removes selected rows. Back up important data and verify object names first.

Example

DROP TABLE scores;

Expected result: The scores table itself is removed.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Quick review

Explain each concept in your own words. Recreate one example without looking at the code. Compare your result with the expected result above.

Browse the full SQL learning path. This is original teaching material; the syllabus was checked against the w3resource SQL tutorial index.