Rules that protect your data
A good table design stops bad data at the door: no order without a customer, no negative prices, no duplicate emails. You declare the rules once with types and constraints, and the database enforces them forever — no matter which app writes the data.
CREATE TABLE
CREATE TABLE staff (
id serial PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
role text NOT NULL CHECK (role IN ('chef', 'waiter', 'manager')),
hourly_rate numeric(8, 2) NOT NULL CHECK (hourly_rate > 0),
active boolean NOT NULL DEFAULT true,
hired_on date NOT NULL DEFAULT current_date
);
INSERT INTO staff (name, email, role, hourly_rate) VALUES ('Asha', '[email protected]', 'chef', 450.50);
SELECT * FROM staff;
INSERT INTO staff (name, email, role, hourly_rate) VALUES ('Bikash', '[email protected]', 'waiter', 300);- Common types: int/bigint, numeric(p, s), text, boolean, date, timestamptz (timestamps with time zone), uuid, jsonb.
- Changing a table later: ALTER TABLE staff ADD COLUMN phone text;
- DROP TABLE staff; deletes the table *and all its data* — careful.