One Membership per Student per Club

Create memberships to record which students belong to which clubs: id INTEGER PRIMARY KEY; student_id INTEGER NOT NULL that REFERENCES students(id); club_id INTEGER NOT NULL (no clubs table exists yet, so no foreign key here); joined_on TEXT NOT NULL DEFAULT CURRENT_DATE; and a table-level UNIQUE (student_id, club_id). A student may join many clubs and a club may have many students, but the same student joining the same club twice must be refused, as must a student id that does not exist.

Expected result:

Table memberships
✓ id INTEGER PRIMARY KEY
✓ student_id INTEGER NOT NULL
✓ club_id INTEGER NOT NULL
✓ joined_on TEXT NOT NULL DEFAULT CURRENT_DATE
✓ student_id REFERENCES students(id)
✓ UNIQUE (club_id, student_id)
✓ Student 1 joining club 1 twice: rejected (UNIQUE)
✓ Student 1 joining clubs 1 and 2: accepted
✓ Students 1 and 2 both joining club 1: accepted
✓ Student 99 joining club 1: rejected (FOREIGN KEY)

Hints3 available

Topics:

CREATE TABLE

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.