Database constraints for data integrity

7360 views

                  class AddConstraintsToUsers < ActiveRecord::Migration[6.1]
  def change
    # Null constraints
    change_column_null :users, :email, false
    change_column_null :users, :username, false

    # Unique constraints
    add_index :users, :email, unique: true
    add_index :users, 'LOWER(username)', unique: true, name: 'index_users_on_lower_username'

    # Check constraints
    execute <<-SQL
      ALTER TABLE users
      ADD CONSTRAINT check_email_format
      CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
    SQL

    execute <<-SQL
      ALTER TABLE users
      ADD CONSTRAINT check_username_length
      CHECK (char_length(username) >= 3 AND char_length(username) <= 30);
    SQL
  end
end

While ActiveRecord validations catch most invalid data, database constraints provide a safety net that prevents invariant violations even when validations are bypassed. I add null: false constraints for required columns, unique indexes for uniqueness constraints, and check constraints for domain validations like age > 0. Foreign key constraints with foreign_key: true prevent orphaned records when associations are deleted. The combination of validations (fast, user-friendly errors) and constraints (absolute guarantees) provides defense in depth. When a constraint is violated, Rails raises ActiveRecord::NotNullViolation or similar exceptions that I handle in error handlers. Constraints are especially important in applications with background jobs or data imports that might skip validation.

Alex Kumar

More from Alex Kumar

Database constraints for data integrity

0 Comments

More from Alex Kumar