foreignkeys

Foreign keys are a database concept used in relational databases to establish and enforce a link between data in two tables. A foreign key is a column or set of columns in one table that reference the primary key (or a unique key) of another table. The relationship models real-world associations, most commonly one-to-many, where many child rows correspond to a single parent row. When defined as a constraint, the database enforces referential integrity: any value in the foreign key column must exist in the referenced table, unless the value is NULL and nulls are allowed.

Foreign key constraints are declared at table creation or added later with alter table. The syntax typically specifies the foreign key columns, the referenced table and columns, and optional actions such as ON DELETE and ON UPDATE. Common actions include CASCADE (propagate changes), SET NULL, SET DEFAULT, RESTRICT or NO ACTION. Different database systems have variations in supported options and default behaviors.

Practical considerations include data integrity, as foreign keys prevent orphaned records and enable meaningful joins. Indexing on foreign keys improves performance of lookups and referential checks. Constraints can be named to aid maintenance and error messages. Some storage engines or configurations disable foreign key enforcement, such as MySQL’s MyISAM engine, while others enforce them strictly (e.g., InnoDB). Self-referential foreign keys are possible for hierarchical data. In many ORMs, foreign keys are represented as associations and are used to generate migrations and queries. Example: CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE);