Foreign Key SQL: Everything You Need To Know About Foreign Key Operations

Foreign Key SQL

In SQL databases, relationships between tables are created using foreign keys. A foreign key is a column or a set of columns in one table that refers to the primary key of another table. This relationship ensures data integrity and consistency across tables. Foreign keys allow for cascading updates and deletes, which can simplify database management.

A foreign key in SQL is a column or set of columns in a table that references a primary key in another table. The purpose of a foreign key is to establish a relationship between two tables, allowing you to enforce referential integrity and ensure data consistency between related tables.

A foreign key in one table points to a primary key in another table. When a row is inserted into the table with the foreign key, the values in the foreign key columns must match the values in the referenced primary key columns. This ensures that the data in the two tables remain consistent, and prevents data from being inserted into the foreign key table that does not match a row in the primary key table.

What is a Foreign Key constraint?

A foreign key is a type of key used to link two tables in a database. So, a foreign key is an attribute or a collection of attributes in one table that refers to the primary key in another table.

For Example, if Table A and Table B are related to each other, then if Table A consists of the primary key, this table would be called the referenced table or parent table. Similarly, if Table B consists of a foreign key, then that table is known as the referencing table or child table. Refer to the below image:

Now that you know what is a foreign key, next in this article on Foreign key SQL, let us understand the rules of the foreign key.

Rules for Foreign key

The Rules of Foreign Key are as follows:

1. The table with the foreign key is called the child table and the table referenced by the foreign key is called the parent table.
2. Null values are allowed in a foreign key
3. Foreign keys can be duplicated
4. There can be more than a single foreign key in a table
5. The relationship established between the tables is known as referential integrity 

Now that you know what are the rules of a foreign key, next in this article on Foreign key SQL, let us see the operations of the foreign key.

Foreign Key Operations:

Foreign key operations in SQL involve defining foreign keys, creating relationships between tables, and performing cascading updates and deletes. To define a foreign key, you must specify the referencing table and column(s) and the referenced table and column(s). Once a foreign key is defined, you can create relationships between tables by inserting data into the referencing table that refers to data in the referenced table.

Cascading updates and deletes allow for automatic updates and deletes of related data. For example, if a record in the referenced table is updated or deleted, the corresponding records in the referencing table can be updated or deleted as well. This feature can simplify database management and ensure data consistency.

Here is an example of using foreign keys in SQL operations:

Suppose you have two tables, departments and employees:

-- departments tableCREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);
-- employees tableCREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments (id)
);

• INSERT: To insert data inemployee’sployee’s table, you need to ensure that the department_id value you provide matches with an id value in the department’s table. For example:

INSERT INTO departments (id, name) VALUES (1, 'Sales');
INSERT INTO departments (id, name) VALUES (2, 'Marketing');
INSERT INTO employees (id, name, department_id) VALUES (1, 'John Doe', 1);
INSERT INTO employees (id, name, department_id) VALUES (2, 'Jane Doe', 2);

Output:

• UPDATE: If you update the department_id value in the employee’s table, you need to ensure that the new value still matches an id value in the department’s table. For example:

UPDATE employees SET department_id = 2 WHERE id = 1;

Output:

• DELETE: If you try to delete a row from the department’s table that is referenced by a foreign key in the employee’s table, you will get an error. This is to prevent data inconsistencies and ensure referential integrity. For example:

DELETE FROM departments WHERE id = 1; 

— Error: Cannot delete or update a parent row: a foreign key constraint fails

• JOIN: You can join the employees and departments tables to retrieve information from both tables based on the relationship established by the foreign key. For example:

SELECTemployees.nameAS employee_name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

Output:

This query returns a result set with the employee_name and department_name columns, based on the relationship between the employees and departments tables.

Drop Foreign Key

o drop a foreign key in SQL, you can use the ALTER TABLE command along with the DROP CONSTRAINT clause.

The syntax is as follows:

ALTER TABLE tablename
DROP CONSTRAINT constraint_name;

Where table name is the name of the table containing the foreign key, and constraint_name is the name of the foreign key constraint you want to drop.

Here is an example:

ALTER TABLE employees
DROP CONSTRAINT fk_department_id;

This statement drops the foreign key constraint named fk_department_id from the employee’s table.

With this, we come to the end of this article. I hope you understood how to use Foreign Key in SQL.

Conclusion:

Foreign keys are an important aspect of SQL databases that allow for relationships between tables and ensure data integrity and consistency. Defining foreign keys, creating relationships between tables, and performing cascading updates and deletes are all important operations in SQL. By understanding foreign keys and their operations, you can create more efficient and effective SQL databases.

FAQ’s

What is a Foreign Key in SQL?

A Foreign Key is a field in a relational database table that is used to establish a relationship between two tables. It refers to the primary key of another table.

What is the purpose of a Foreign Key?

A Foreign Key is used to enforce referential integrity, which ensures that data entered into the database is consistent and accurate.

What is the syntax for creating a Foreign Key in SQL?

ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES referenced_table (referenced_column)
ON DELETE [CASCADE|SET NULL|SET DEFAULT|RESTRICT]