database-relations/foreign-keys

20 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Foreign keys maintain referential integrity

Referential integrity means relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records (records that reference non-existent parent records) and control what happens when deleting a parent record via CASCADE, SET NULL, or other delete actions.

Foreign key purposes

Foreign key constraints serve multiple purposes: (1) explicitly defining and enforcing relationships between tables at the database level, (2) maintaining referential integrity by preventing orphaned records and keeping relationships consistent over time, (3) facilitating database design and understanding by serving as documentation of relationships, and (4) making databases more robust, reliable, and understandable.

Foreign key prevents orphaned records

Foreign key constraints prevent orphaned records, which are records that exist in a child table but don't have a corresponding parent record. For example, an order without a corresponding customer. Without foreign keys, you could accidentally delete a customer while their orders still exist, breaking the logical structure of data.

Foreign key actions type definition

Foreign key actions use the type UpdateDeleteAction: 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default'. They are passed to the references() method as a second argument with an actions object containing optional onUpdate and onDelete properties.

Foreign key CASCADE action

CASCADE action on delete or update: When a row in the parent table is deleted or updated, all corresponding rows in the child table are also deleted or updated accordingly, preventing orphaned rows.

Foreign key NO ACTION default

NO ACTION is the default foreign key action. It prevents deletion or update of a parent row if related rows exist in the child table, causing the operation to fail.

Foreign key RESTRICT action

RESTRICT action prevents deletion or update of a parent row if dependent rows exist in the child table. It is functionally identical to NO ACTION and included for compatibility reasons.

Foreign key SET NULL action

SET NULL action: When a row in the parent table is deleted or updated, the foreign key column in the child table is set to NULL. Requires the foreign key column to allow NULL values.

Foreign key SET DEFAULT action

SET DEFAULT action: When a row in the parent table is deleted or updated, the foreign key column in the child table is set to its default value. If no default value exists, the operation fails.

Foreign key action in references method

Foreign key actions can be specified using the second argument of the references() method. Example: integer('author').references(() => users.id, { onDelete: 'cascade' }).

Foreign key action with foreignKey operator

When using the foreignKey() operator, foreign key actions are chained using .onDelete() and .onUpdate() methods. Example: foreignKey({ columns: [...], foreignColumns: [...] }).onDelete('cascade').onUpdate('cascade').

PostgreSQL foreign key with cascade delete in Drizzle

To create a foreign key with cascade delete in Drizzle PostgreSQL schema, use the references() method on an integer column with onDelete set to "cascade": p.integer().notNull().references(() => usersTable.id, { onDelete: "cascade" })

Foreign key constraint with cascading delete

In PostgreSQL schema definition, specify a foreign key that cascades on delete with: userId: integer('user_id').notNull().references(() => usersTable.id, { onDelete: 'cascade' }).

Foreign key constraint with references

Define a foreign key using .references(() => targetTable.column, { onDelete: "cascade" }). The onDelete option specifies cascade to delete child rows when parent is deleted, or no action to prevent deletion.

Foreign keys define and enforce relationships at the database level

Foreign key constraints explicitly define and enforce relationships between tables. They tell the database to enforce a relationship such as one-to-many: every value in the foreign key column must correspond to a valid value in the primary key column of the referenced table. The database actively enforces this constraint and becomes relationship-aware because of the foreign key.

Foreign keys maintain referential integrity and prevent orphaned records

Referential integrity means relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records, which are records that exist in one table but do not have a corresponding record in the related table (for example, an order without a corresponding customer). Without foreign key constraints, you could accidentally delete a customer while their orders still exist, leaving orders pointing to a non-existent customer. Foreign key constraints prevent this data inconsistency or control what happens via actions like CASCADE or SET NULL.

Foreign keys serve as database design documentation

Foreign keys are a crucial part of database design documentation. When you see a foreign key in a database schema, it immediately communicates the relationship between tables. This makes databases easier to understand, maintain, and evolve over time, allowing new developers to quickly grasp how different parts of the database are connected.

Performance overhead of foreign keys in high-write environments

In extremely high-volume transactional systems (such as real-time logging, high-frequency trading platforms, or massive IoT data ingestion), foreign key constraints can introduce a small but potentially noticeable performance overhead. Every insert or update in a table with a foreign key requires the database to perform referential integrity checks, which can accumulate in very high-write scenarios.

Foreign keys in distributed database systems add complexity

In distributed database systems where data is distributed across multiple database nodes or clusters (common in sharded databases, cloud environments, and microservices), cross-node foreign keys can introduce significant complexity and performance overhead. Validating referential integrity requires communication between nodes, leading to increased latency. Distributed transactions needed to maintain consistency are more complex and less performant than local transactions. Application-level data integrity checks or eventual consistency models might be considered as alternatives.

Foreign keys challenges with legacy systems and non-relational data

When integrating a relational database with legacy systems or non-relational data stores (such as NoSQL, flat files, or external APIs), foreign key constraints can create problems. Legacy systems or non-relational data might not consistently adhere to referential integrity rules enforced by foreign keys. Imposing foreign keys can lead to data import issues and data inconsistencies. In such scenarios, application logic or ETL processes may be required to ensure data integrity instead of strictly enforcing foreign keys at the database level.

Give your agent this brain