Library › Coding AI › College students

Write SQLite tables from a spec with your team's rules

Practice: Setting limits and rules. About 5 minutes.

About this exercise

Your study-group app's spec is below. Your team's rules: start with PRAGMA foreign_keys = ON;, name the tables exactly study_groups and group_members, and make both STRICT tables. group_members uses a composite primary key (group_id, user_id), its foreign key to study_groups uses ON DELETE CASCADE, and role defaults to 'member' and is limited with a CHECK to 'owner' or 'member'. Exactly 1 code block of at most 20 lines, under 20 words of text.

Common mistakes when you ask AI for this

  • Foreign keys switched off. SQLite ignores foreign keys unless you turn them on, so the cascade never runs. Try: Add: "Start with PRAGMA foreign_keys = ON;"
  • Loose column types. Without STRICT, SQLite lets any value into any column. Try: Add: "Make both tables STRICT."
  • Letting the AI name tables. The AI picks groups and memberships, which won't match your team's queries. Try: Add: "Tables exactly study_groups and group_members; composite key (group_id, user_id)."
  • No default role. The AI won't default new members to 'member', and may add 'moderator' from the spec. Try: Add: "role DEFAULT 'member', CHECK role IN ('owner', 'member') only."
  • Extra tables and notes. The AI adds a users table, indexes and an explanation. Try: Add: "Only these two tables, one code block, no text."

What a strong prompt includes

  • Turns on foreign keys
  • Asks for STRICT tables
  • Names the tables
  • Gives the key and cascade
  • Limits the roles

Practice this now ↑

Related exercises