Summary
Once we have populated the sic_url_identifier for cusi_case rows, we should remove the default on the column and make the index unique per T409316: Add a column to cusi_case for a URL identifier.
Background
- In T409316: Add a column to cusi_case for a URL identifier we added the sic_url_identifier column to cusi_case
- This column was populated for new rows in T409555: Suggested investigations: Populate sic_url_identifier when creating new cases and for existing rows in T409564: Suggested investigations: Populate sic_url_identifier for existing cusi_case rows
- Once these are done we can remove the default on the column and make the associated index unique
- This column was populated for new rows in T409555: Suggested investigations: Populate sic_url_identifier when creating new cases and for existing rows in T409564: Suggested investigations: Populate sic_url_identifier for existing cusi_case rows
Proposed schema
Per T409316: Add a column to cusi_case for a URL identifier, alter cusi_case table to be the following:
> describe cusi_case; +-----------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------+----------------+ | sic_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | sic_status | tinyint(3) unsigned | NO | MUL | 0 | | | sic_status_reason | varbinary(255) | NO | | | | | sic_created_timestamp | binary(14) | NO | MUL | NULL | | | sic_url_identifier | int(10) unsigned | NO | UNI | NULL | | +-----------------------+---------------------+------+-----+---------+----------------+
> show indexes from cusi_case; +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ ... | cusi_case | 0 | sic_url_identifier | 1 | sic_url_identifier | A | 1 | NULL | NULL | | BTREE | | | NO | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
Acceptance criteria
- Schema change is reviewed and finalised
- Schema change is applied to the CheckUser extension master branch
- A Schema-change-in-production ticket is filed to make the schema change - T410631