fix: ensure teams primary key exists before adding foreign key#196
fix: ensure teams primary key exists before adding foreign key#196ManukMinasyan merged 1 commit intomainfrom
Conversation
The ULID migration can leave teams.id without a primary key on PostgreSQL. Add a pre-check that restores the constraint before creating the foreign key on personal_access_tokens.
There was a problem hiding this comment.
Pull request overview
Adds a PostgreSQL-specific pre-check in the personal_access_tokens.team_id migration to repair a missing teams.id primary key before creating the foreign key constraint, preventing failures after the ULID migration sequence.
Changes:
- Adds a
ensureTeamsPrimaryKey()pre-check executed before adding theteam_idforeign key. - Uses a
pg_constraintlookup to detect whetherteamshas a primary key, and re-adds one onidif missing.
| $hasPrimary = DB::selectOne( | ||
| "SELECT 1 FROM pg_constraint WHERE conrelid = 'teams'::regclass AND contype = 'p'" | ||
| ); | ||
|
|
||
| if (! $hasPrimary) { |
There was a problem hiding this comment.
The pre-check only verifies that the teams table has some primary key (contype = 'p'), but the foreign key requires that the referenced column (teams.id) is unique/primary. As written, this can still fail if teams has a primary key on a different column, and it can also add a redundant primary key/index when teams.id is already covered by a unique constraint. Consider checking specifically for a primary/unique constraint that includes the id column (e.g., via pg_index/pg_attribute), and only adding a constraint when teams.id is not already unique.
| $hasPrimary = DB::selectOne( | |
| "SELECT 1 FROM pg_constraint WHERE conrelid = 'teams'::regclass AND contype = 'p'" | |
| ); | |
| if (! $hasPrimary) { | |
| $idIsUnique = DB::selectOne(" | |
| SELECT 1 | |
| FROM pg_index i | |
| JOIN pg_class c ON c.oid = i.indrelid | |
| JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey) | |
| WHERE c.relname = 'teams' | |
| AND i.indisunique | |
| AND i.indnatts = 1 | |
| AND a.attname = 'id' | |
| LIMIT 1 | |
| "); | |
| if (! $idIsUnique) { |
| $hasPrimary = DB::selectOne( | ||
| "SELECT 1 FROM pg_constraint WHERE conrelid = 'teams'::regclass AND contype = 'p'" | ||
| ); | ||
|
|
||
| if (! $hasPrimary) { |
There was a problem hiding this comment.
DB::selectOne() returns an object or null, but $hasPrimary is treated as a boolean. For clarity (and to avoid surprising truthiness), consider renaming to something like $primaryConstraintExists and using an explicit null check (e.g., === null) rather than if (! $hasPrimary).
| $hasPrimary = DB::selectOne( | |
| "SELECT 1 FROM pg_constraint WHERE conrelid = 'teams'::regclass AND contype = 'p'" | |
| ); | |
| if (! $hasPrimary) { | |
| $primaryConstraintExists = DB::selectOne( | |
| "SELECT 1 FROM pg_constraint WHERE conrelid = 'teams'::regclass AND contype = 'p'" | |
| ); | |
| if ($primaryConstraintExists === null) { |
The ULID migration can leave teams.id without a primary key on PostgreSQL. Add a pre-check that restores the constraint before creating the foreign key on personal_access_tokens.