Yes, a foreign key can reference a composite primary key, which is a primary key made up of two or more columns. This is common in linking tables used to model many-to-many relationships. To implement this, the foreign key in the referencing table must match all columns that make up the composite key in both order and data type. For example, consider an ‘Enrolments’ table with a composite primary key made up of StudentID and CourseID. If another table, such as ‘Grades’, needs to reference specific enrolment records, it must include both StudentID and CourseID as a foreign key pair. These foreign keys together would reference the composite key in the Enrolments table. This implementation ensures that every foreign key reference uniquely identifies a valid enrolment. The use of composite keys and foreign keys together allows for very precise and controlled relational structures but adds complexity to query design and maintenance.

Yes, but only if the referenced column has a unique constraint applied. In most relational database systems, a foreign key can refer either to a primary key or to a column (or combination of columns) that is guaranteed to have unique values. This ensures that the foreign key relationship still upholds referential integrity, as each referenced value must exist uniquely in the parent table. However, referencing a non-primary key column is less common in practice, because primary keys are designed specifically to uniquely identify records. When a unique constraint is used instead, the referenced column must still ensure that no duplicate or null values exist, maintaining consistency for lookups and joins. Using such references can be useful in scenarios where alternative identifiers (such as email addresses or national ID numbers) are used as logical keys. Nevertheless, for clarity and conventional design, primary keys are the preferred targets for foreign key relationships.

Yes, a table can have multiple foreign keys, each referencing a different parent table or even different columns in the same parent table. This is common in databases that model complex relationships. For example, an ‘Appointments’ table in a medical database might include a PatientID foreign key referencing the ‘Patients’ table and a DoctorID foreign key referencing the ‘Doctors’ table. This design enables the same table to be logically linked to multiple entities. The main implication of using multiple foreign keys is increased structural integrity and accurate modelling of real-world relationships. However, it also increases the complexity of the database schema and requires careful management of referential actions (such as cascading updates or deletes). Developers must ensure that all foreign key constraints are respected when inserting, updating, or deleting records. Query performance can also be impacted if the number of joins and lookups grows too large without appropriate indexing.

A self-referencing foreign key is a foreign key within a table that refers back to the primary key of the same table. This is used to model hierarchical or recursive relationships within a single entity. For example, in an ‘Employees’ table, a ManagerID column might serve as a foreign key that points to the EmployeeID primary key in the same table, allowing the database to represent an organisational structure. In contrast, a regular foreign key links two separate tables, such as CustomerID in an ‘Orders’ table referencing the ‘Customers’ table. The purpose of both types is to enforce referential integrity, but self-referencing foreign keys are particularly useful for tree-like data structures, such as organisational charts, category hierarchies, or threaded discussions. Implementing self-referencing keys requires extra care with recursive queries and may involve specialised SQL techniques such as Common Table Expressions (CTEs) to traverse the relationships.

Common errors when using foreign keys include referencing a non-existent parent record, violating data type consistency, attempting to delete a parent record without handling related child records, and inserting null values into non-nullable foreign key fields. To avoid referencing non-existent records, ensure that the parent record exists before inserting into the child table. Enforce referential integrity strictly through constraints and ensure the foreign key column and referenced column have matching data types and formats. Attempting to delete a parent without handling its children can be managed using referential actions like ON DELETE CASCADE, SET NULL, or RESTRICT. Additionally, when using ORMs or application-layer data handlers, ensure that transactional operations are structured so that inserts, updates, and deletes occur in a logical order. Indexing foreign keys can prevent performance issues during large joins. Validating foreign key rules during database schema design and testing helps catch these issues early and ensures a robust relational model.