Analysts mostly read data, but interviews often include table design questions, and understanding how tables are built helps you query them correctly. Constraints are the rules that keep data clean in the first place.
A small schema
A fictional clinic chain, CareWell Clinics, needs patients, doctors and appointments.
CREATE TABLE patients (
patient_id INTEGER PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth_date DATE,
city VARCHAR(50) DEFAULT 'Unknown'
);
CREATE TABLE doctors (
doctor_id INTEGER PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
speciality VARCHAR(50) NOT NULL
);
CREATE TABLE appointments (
appointment_id INTEGER PRIMARY KEY,
patient_id INTEGER NOT NULL REFERENCES patients(patient_id),
doctor_id INTEGER NOT NULL REFERENCES doctors(doctor_id),
scheduled_at TIMESTAMP NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'Booked'
CHECK (status IN ('Booked', 'Completed', 'Cancelled', 'No-show')),
fee DECIMAL(8, 2) CHECK (fee >= 0)
);Choosing data types
| Data | Type | Why |
|---|---|---|
| IDs, counts | INTEGER | Whole numbers, fast to join |
| Money | DECIMAL(p, s) | Exact; FLOAT can create rounding errors |
| Names, codes | VARCHAR(n) | Variable-length text |
| Phone numbers, PIN codes | VARCHAR | They are labels, not quantities; leading zeros matter |
| Dates | DATE | Enables date maths and correct sorting |
| Date and time | TIMESTAMP | When the time of day matters |
| Yes/no | BOOLEAN | Clear and compact |
Storing dates or numbers as text is one of the most common causes of messy data: sorting breaks and calculations fail.
Constraints, one by one
- PRIMARY KEY: uniquely identifies each row; cannot be NULL or duplicated.
- FOREIGN KEY (REFERENCES): the value must exist in another table. You cannot book an appointment for a patient who does not exist.
- NOT NULL: the column must always have a value.
- UNIQUE: no two rows can share the value (two patients cannot share a phone number here).
- CHECK: the value must satisfy a condition (fee not negative, status from an allowed list).
- DEFAULT: the value used when none is given.
Why constraints matter to analysts
Every rule enforced in the database is one less cleaning step later. Without a CHECK on status, you would eventually find "completed", "Complete" and "done" all meaning the same thing. Without foreign keys, you would find appointments for patient IDs that do not exist, and your joins would silently drop them.
Changing tables later
ALTER TABLE patients ADD COLUMN email VARCHAR(100);
ALTER TABLE patients DROP COLUMN email;
DROP TABLE appointments; -- removes the table and its dataKnow the difference between DELETE (removes rows, can use WHERE), TRUNCATE (removes all rows quickly) and DROP (removes the whole table). It is a frequent interview question.
Views
A view is a saved query that behaves like a table:
CREATE VIEW completed_visits AS
SELECT a.appointment_id, p.full_name, d.speciality, a.fee
FROM appointments a
JOIN patients p ON p.patient_id = a.patient_id
JOIN doctors d ON d.doctor_id = a.doctor_id
WHERE a.status = 'Completed';Practise
Work through CREATE TABLE, constraints, INSERT, UPDATE and DELETE and ALTER and views. Each question runs in your browser, so you can see exactly what a constraint blocks.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: