SELF-CHECK
Review Questions for Lesson 5
Check to see if you can confidently answer these 8 questions during your Presentation 5 defense
1. IDENTITY vs. serial
2. Composite foreign key
How does `GENERATED ALWAYS AS IDENTITY` differ from the deprecated `serial` macro, and what kind of error does it prevent?
Why use a composite foreign key (match_id, killer_id) in the kill_log instead of a single reference to the players table (player_id)?
3. ON DELETE Policy
4. The Docker Volume Trap
In which tables of the game project should you set ON DELETE RESTRICT, where should you set SET NULL, and where should you set CASCADE?
Why aren't changes to a file in /docker-entrypoint-initdb.d/ applied after a normal `docker compose restart`?
5. Roles and SEQUENCES
6. Protection of the purchase log
Why isn't it enough for the `game_app` role to be granted only `GRANT INSERT ON TABLE` to insert into a table with an `IDENTITY` column?
How can I configure the `purchases` table in PostgreSQL so that the service can only append to it (Append-Only) at the privilege level?
7. pg_dump -Fp vs. -Fc
8. COPY vs. \copy
What is the difference between the -Fp and -Fc formats in pg_dump, and why can't a binary dump be fed into psql?
How does the COPY server command differ from the \copy metacommand in psql in terms of permissions and file location?