Relax subscription_add_on_id constraints on add-on purchases

What does this MR do and why?

Issue: customers-gitlab-com#15787 (closed) (Track B, step B1)

Prepares subscription_add_on_purchases.subscription_add_on_id for removal ahead of the FixedItemsModel swap in customers-gitlab-com#15784, which stops writing the column. Three post-deploy migrations:

• drop the foreign key fk_410004d68b (subscription_add_on_id → subscription_add_ons(id))
• drop the NOT NULL on the column (the column itself is dropped later, in B2)
• drop the three id-based indexes

Uniqueness stays enforced: index_add_on_purchases_on_add_on_uid_and_namespace_id_not_null is UNIQUE (subscription_add_on_uid, namespace_id) NULLS NOT DISTINCT, and check_c4de34843d enforces uid presence. index_subscription_add_on_purchases_on_namespace_id_add_on_id stays until the column drop to back the model's subscription_add_on_id uniqueness validation.

customers-gitlab-com#15784 must ship at least one milestone after this merges.

Database review

db:migrate

main: == 20260803210112 RemoveFkSubscriptionAddOnPurchasesToSubscriptionAddOns: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE subscription_add_ons, subscription_add_on_purchases IN ACCESS EXCLUSIVE MODE")
main:    -> 0.0003s
main: -- remove_foreign_key(:subscription_add_on_purchases, :subscription_add_ons, {:name=>:fk_410004d68b})
main:    -> 0.0016s
main: == 20260803210112 RemoveFkSubscriptionAddOnPurchasesToSubscriptionAddOns: migrated (0.0478s) 

main: == 20260803210113 RemoveNotNullFromSubscriptionAddOnPurchasesAddOnId: migrating 
main: -- change_column_null(:subscription_add_on_purchases, :subscription_add_on_id, true, nil)
main:    -> 0.0274s
main: == 20260803210113 RemoveNotNullFromSubscriptionAddOnPurchasesAddOnId: migrated (0.0315s) 

main: == 20260803210114 DropLegacyAddOnIdIndexesFromSubscriptionAddOnPurchases: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0261s
main: -- index_name_exists?(:subscription_add_on_purchases, "index_add_on_purchases_on_add_on_id_and_namespace_id_not_null")
main:    -> 0.0007s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:subscription_add_on_purchases, {:algorithm=>:concurrently, :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_not_null"})
main:    -> 0.0013s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0002s
main: -- index_name_exists?(:subscription_add_on_purchases, "index_add_on_purchases_on_add_on_id_and_namespace_id_null")
main:    -> 0.0003s
main: -- remove_index(:subscription_add_on_purchases, {:algorithm=>:concurrently, :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_null"})
main:    -> 0.0006s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0002s
main: -- index_name_exists?(:subscription_add_on_purchases, "idx_subscription_add_on_purchases_on_subscription_add_on_id")
main:    -> 0.0003s
main: -- remove_index(:subscription_add_on_purchases, {:algorithm=>:concurrently, :name=>"idx_subscription_add_on_purchases_on_subscription_add_on_id"})
main:    -> 0.0006s
main: == 20260803210114 DropLegacyAddOnIdIndexesFromSubscriptionAddOnPurchases: migrated (0.0777s) 

db:migrate:down

main: == 20260803210114 DropLegacyAddOnIdIndexesFromSubscriptionAddOnPurchases: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0275s
main: -- index_exists?(:subscription_add_on_purchases, [:subscription_add_on_id, :namespace_id], {:unique=>true, :where=>"namespace_id IS NOT NULL", :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_not_null", :algorithm=>:concurrently})
main:    -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:subscription_add_on_purchases, [:subscription_add_on_id, :namespace_id], {:unique=>true, :where=>"namespace_id IS NOT NULL", :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_not_null", :algorithm=>:concurrently})
main:    -> 0.0035s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0002s
main: -- index_exists?(:subscription_add_on_purchases, :subscription_add_on_id, {:unique=>true, :where=>"namespace_id IS NULL", :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_null", :algorithm=>:concurrently})
main:    -> 0.0016s
main: -- add_index(:subscription_add_on_purchases, :subscription_add_on_id, {:unique=>true, :where=>"namespace_id IS NULL", :name=>"index_add_on_purchases_on_add_on_id_and_namespace_id_null", :algorithm=>:concurrently})
main:    -> 0.0015s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0002s
main: -- index_exists?(:subscription_add_on_purchases, :subscription_add_on_id, {:name=>"idx_subscription_add_on_purchases_on_subscription_add_on_id", :algorithm=>:concurrently})
main:    -> 0.0017s
main: -- add_index(:subscription_add_on_purchases, :subscription_add_on_id, {:name=>"idx_subscription_add_on_purchases_on_subscription_add_on_id", :algorithm=>:concurrently})
main:    -> 0.0008s
main: == 20260803210114 DropLegacyAddOnIdIndexesFromSubscriptionAddOnPurchases: reverted (0.0623s) 

main: == 20260803210113 RemoveNotNullFromSubscriptionAddOnPurchasesAddOnId: reverting 
main: == 20260803210113 RemoveNotNullFromSubscriptionAddOnPurchasesAddOnId: reverted (0.0039s) 

main: == 20260803210112 RemoveFkSubscriptionAddOnPurchasesToSubscriptionAddOns: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE subscription_add_ons, subscription_add_on_purchases IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0003s
main: -- execute("ALTER TABLE subscription_add_on_purchases ADD CONSTRAINT fk_410004d68b FOREIGN KEY (subscription_add_on_id) REFERENCES subscription_add_ons (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0009s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE subscription_add_on_purchases VALIDATE CONSTRAINT fk_410004d68b;")
main:    -> 0.0048s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20260803210112 RemoveFkSubscriptionAddOnPurchasesToSubscriptionAddOns: reverted (0.0566s) 

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.