FOREIGN KEY constraint
Assume there is the departments table with the columns department_id INT PRIMARY KEY and department_name VARCHAR(20) NOT NULL:
To create the employees table, the columns include employee_id INT PRIMARY KEY, name VARCHAR(60), and department_id INT. An employee's department affiliation is limited to existing departments, ensuring that all department_id values in the employees table align with corresponding values in the departments table.
To establish this connection between the two tables, department_id in the employees table can be designated as a foreign or referencing key. The database management system will oversee the data permissible in the employees table's foreign key column.
Note that the FOREIGN KEY constraints can only reference tables within the same database.
To mark a field or a group of fields as a foreign key, use the FOREIGN KEY constraint and create the table employees. In the query, the REFERENCES keyword specifies the table and the primary key column or columns (in parentheses) with unique values to which the foreign key points. The structure and data type of the primary and foreign keys must be the same. The table that the foreign key points to is called the referenced or parent table. Here is what the query will look like:
After the query execution, the table employees becomes a child table, that is, a table containing the foreign key.
Now, insert a tuple (1, 'Ann Riding', 4) in employees will cause an error because the table departments does not have a row with such a department.
With the query above, there is a named foreign key created. It is good practice to use only named foreign keys because, in some SQL dialects (including MySQL), unnamed ones may cause trouble in the future. However, there is still an option to create a table with an unnamed foreign key:
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
• Focus on practice and real-world experience