Constraints & Referential Integrity Review Skill

Constraints & Referential Integrity Review

Review database constraints, foreign keys, and data integrity rules.

Database-Level Enforcement

# PROBLEM: Model-only validation (race condition vulnerable)
validates :email, uniqueness: true

# SOLUTION: Database constraint + model validation
add_index :users, :email, unique: true
validates :email, uniqueness: true

Race Condition Prevention

# PROBLEM: Check-then-insert race condition
def claim_slot
  return false if Slot.where(user_id: user.id).exists?
  Slot.create!(user_id: user.id)
end

# SOLUTION: Database constraint with rescue
def claim_slot
  Slot.create!(user_id: user.id)
rescue ActiveRecord::RecordNotUnique
  false
end

Missing Constraints Checklist

| Validation | Required DB Constraint | |------------|----------------------| | presence: true | NOT NULL | | uniqueness: true | Unique index | | belongs_to | Foreign key | | enum | Check constraint | | numericality: { in: } | Check constraint |

Foreign Key Cascades

# PROBLEM: Orphaned records on deletion
has_many :comments

# SOLUTION: Dependent handling
has_many :comments, dependent: :destroy  # For callbacks
# OR database-level:
add_foreign_key :comments, :posts, on_delete: :cascade

Polymorphic Associations

# PROBLEM: No referential integrity for polymorphics
belongs_to :commentable, polymorphic: true

# SOLUTION: Validate type + consider alternatives
ALLOWED_TYPES = %w[Post Article].freeze
validates :commentable_type, inclusion: { in: ALLOWED_TYPES }

# Better: separate tables instead of polymorphic
belongs_to :post, optional: true
belongs_to :article, optional: true
validates :post_id, presence: true, unless: :article_id?

Orphan Detection Queries

# Find orphaned comments (missing parent)
Comment.left_joins(:post).where(posts: { id: nil })

# Find missing foreign keys
ActiveRecord::Base.connection.tables.each do |table|
  columns = ActiveRecord::Base.connection.columns(table)
  columns.select { |c| c.name.end_with?('_id') }.each do |col|
    # Check if FK exists
  end
end

Review Checklist

[ ] All uniqueness validations have unique indexes?
[ ] All presence validations have NOT NULL constraints?
[ ] All _id columns have foreign keys?
[ ] Cascade rules defined for deletions?
[ ] Polymorphic types validated?
[ ] No orphaned records possible?

Output Format

## Constraints Review: [PASS/WARN/FAIL]

### Missing Database Constraints
- [model]: [validation without DB enforcement]

### Foreign Key Issues
- [table]: [missing FK on _id column]

### Race Condition Risks
- [code location]: [check-then-insert pattern]

### Orphan Risks
- [association]: [could create orphans]

### Recommendations
1. [Prioritized fixes]

Agent Skills: Constraints & Referential Integrity Review

Install this agent skill to your local

Skill Files