Design challenge
- SQL course
- Expert
- Lesson 42/45
- 6 min
Turn an ER model into tables and add indexes.
Design challenge
The expert final begins. First challenge: design the users table with id SERIAL PRIMARY KEY, email TEXT UNIQUE, password_hash TEXT. This table feeds the whole final.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);Design sets up the base table.
The table users is created.
Practice Design challenge
Create the users table with id SERIAL PRIMARY KEY, email TEXT UNIQUE, password_hash TEXT.
Starter code
-- design
CREATE TABLE usersSample solution
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);Expected output
CREATE TABLE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What is used as the primary key?
- created_at
- email TEXT
- password_hash
- id SERIAL Correct answer
Explanation: id SERIAL is used as the primary key.
2 Email constraint
Question
email TEXT ____
Answer
email TEXT UNIQUEExplanation: UNIQUE prevents the same email address from being stored twice.
3 Design challenge: match the terms
| Term | Meaning |
|---|---|
id |
id |
email |
|
SERIAL |
auto |
UNIQUE |
unique |