github.com/shoshinnikita/budget-manager@v0.7.1-0.20220131195411-8c46ff1c6778/internal/db/pg/migrations/3_add_foreign_key.go (about)

     1  package migrations
     2  
     3  import (
     4  	"database/sql"
     5  
     6  	"github.com/ShoshinNikita/budget-manager/internal/pkg/errors"
     7  )
     8  
     9  func addForeignKeysMigration(tx *sql.Tx) error {
    10  	// Prepare for foreign key constraints
    11  
    12  	// Reset nonexistent spend type ids
    13  	_, err := tx.Exec(`
    14  		UPDATE spends SET type_id = NULL WHERE type_id NOT IN (SELECT id FROM spend_types);
    15  		UPDATE monthly_payments SET type_id = NULL WHERE type_id NOT IN (SELECT id FROM spend_types);
    16  	`)
    17  	if err != nil {
    18  		return errors.Wrap(err, "couldn't reset nonexistent spend type ids")
    19  	}
    20  
    21  	// Add foreign key constraints
    22  	_, err = tx.Exec(`
    23  		ALTER TABLE days
    24  		    ADD FOREIGN KEY (month_id) REFERENCES months(id);
    25  
    26  		ALTER TABLE incomes
    27  		    ADD FOREIGN KEY (month_id) REFERENCES months(id);
    28  
    29  		ALTER TABLE monthly_payments
    30  		    ADD FOREIGN KEY (month_id) REFERENCES months(id),
    31  		    ADD FOREIGN KEY (type_id) REFERENCES spend_types(id);
    32  
    33  		ALTER TABLE spends
    34  		    ADD FOREIGN KEY (day_id) REFERENCES days(id),
    35  		    ADD FOREIGN KEY (type_id) REFERENCES spend_types(id);
    36  	`)
    37  	return err
    38  }