Referential integrity and orphaned records
Referential integrity ensures that relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records—records that reference entities that no longer exist. For example, without foreign key constraints, you could delete a customer from a Customers table while their orders still exist in an Orders table, creating orders with no corresponding customer. Foreign key constraints prevent this data inconsistency, often using rules like CASCADE, SET NULL, etc., to control what happens during deletion.
When to avoid Foreign Keys: high-write environments
In extremely high-volume transactional systems (like real-time logging or high-frequency trading platforms), foreign key constraints can introduce performance overhead because the database must perform referential integrity checks on every insert or update operation.
When to avoid Foreign Keys: distributed database systems
In systems where data is distributed across multiple database nodes or clusters (sharded databases, cloud environments, microservices), cross-node foreign keys can introduce significant complexity and performance overhead. Validating referential integrity requires communication between nodes, increasing latency. Distributed transactions are more complex and less performant than local transactions, so application-level data integrity checks or eventual consistency models might be considered as alternatives.
When to avoid Foreign Keys: legacy and non-relational systems
When integrating relational databases with legacy systems or non-relational data stores (NoSQL, flat files, external APIs), foreign key constraints may be problematic because legacy systems or non-relational data may not adhere to referential integrity rules. Imposing foreign keys in such scenarios can lead to data import issues and inconsistencies. Instead, use application logic or ETL processes to ensure data integrity.
Relations and foreign keys are independent abstractions
Relations and foreign keys serve similar purposes but work at different levels. Foreign keys are database-level constraints checked on insert/update/delete operations. Relations are application-level abstractions used only for querying and do not affect the database schema. They can be used independently or together, and either works with or without the other.
Relations without foreign keys allow use with non-FK databases
Relations can be defined without using database foreign keys, allowing them to work with databases that do not support foreign key constraints. A relation defined with r.one.profileInfo({ from: r.users.id, to: r.profileInfo.userId }) queries identically whether or not the userId column has a .references() foreign key constraint.
MySQL references syntax with AnyMySqlColumn
When defining foreign key references in MySQL Drizzle schema, use the `.references()` method with a callback function that returns the referenced column. Import `AnyMySqlColumn` type from 'drizzle-orm/mysql-core' for type safety. Example: `invitee: t.int().references((): AnyMySqlColumn => users.id)`
Foreign keys purpose beyond validation
Foreign key constraints serve three primary purposes: explicitly defining and enforcing relationships between tables, maintaining referential integrity to prevent orphaned records, and facilitating database design documentation and understanding. They ensure relationships remain consistent and valid over time.
Referential integrity concept
Referential integrity means that relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records (records that exist but don't have corresponding related records in referenced tables). Controls like CASCADE and SET NULL determine what happens when related data is deleted.
When to avoid or use foreign keys cautiously
Foreign keys should be used cautiously in three scenarios: (1) extremely high-write environments where foreign key checks introduce performance overhead, (2) distributed database systems where cross-node foreign keys require expensive inter-node communication, and (3) legacy systems or non-relational data integration where referential integrity cannot be consistently enforced, requiring application-level data integrity checks instead.
Foreign key definition with cascade delete
Foreign keys are defined as: fieldName = this.int('column_name').foreignKey(ReferencedTable, (table) => table.id, { onDelete: 'CASCADE' }); The onDelete option supports 'CASCADE' to automatically delete related records.
Custom names for primary keys and foreign keys
Starting from v0.29.0, you can specify custom names for primaryKey() and foreignKey() constraints using the name property. The syntax is: primaryKey({ name: 'composite_key', columns: [table.id, table.name] }) and foreignKey({ name: 'fkName', columns: [table.id], foreignColumns: [table.name] }). This is useful to avoid exceeding the 64-character database limit for constraint names.