SQL Foreign Key

What is a Foreign Key?

Foreign key as a bridge between two tables. It's a column in one table that shows to the main key in another table, creating a connection that makes your data more meaningful.

Imagine you have two tables: the Customer Details table and the Order detail table. The Order detail table, where customers place their departments, has a column called "cus_id" that points directly to the "cus_id" column in the Customer Detail table. It's like a digital handshake between the two, ensuring every order is associated with a specific customer.

The Primary and the Foreign

The "cus_id" column in the Students table takes on the role of the PRIMARY KEY, uniquely identifying each customer. On the other side, in the Orders table, "cus_id" becomes a FOREIGN KEY, establishing the link back to the Customer Detail table.

The foreign key is great because it stops things from getting messy. The foreign key rule acts like a guard, making sure that nothing messes up the important connections between tables. It quietly protects against wrong data getting into the foreign key column.

Implementing Foreign Keys

Creating Foreign Keys on Table Creation

CREATE TABLE department_detail
(
  dept_id numeric(10) not null,
  dept_name varchar2(50) not null,
  CONSTRAINT department_id PRIMARY KEY (dept_id)
);

CREATE TABLE employee_detail
(
  emp_id numeric(10) not null,
  emp_name varchar2(50) not null,
  contac_person varchar2(50),
  depart_id numeric(10) not null,
  CONSTRAINT pk_emp_id PRIMARY KEY (emp_id),
  CONSTRAINT fk_department_id FOREIGN KEY (depart_id) REFERENCES department_detail(dept_id)
);

• The pk_emp_id constraint is set as the primary key on the emp_id column, ensuring each employee has a unique identifier.
• The fk_department_id constraint establishes a foreign key relationship on the depart_id column, referencing the dept_id column in the department_detail table. This means that the depart_id values in the employee_detail table must correspond to existing dept_id values in the department_detail table, maintaining referential integrity.

Adding Foreign Keys to an Existing Table

ALTER TABLE employee_detail  
ADD CONSTRAINT fk_department_id  
FOREIGN KEY(depart_id)  
REFERENCES department_detail (dept_id);

• ALTER TABLE employee_detail:

Specifies that the modification is applied to the employee_detail table.

• ADD CONSTRAINT fk_department_id:

Introduces a new constraint named fk_department_id. This name is user-defined and could be different based on the naming conventions used.

• FOREIGN KEY(depart_id):

Defines the depart_id column in the employee_detail table as a foreign key. This implies that the values in the depart_id column of the employee_detail table must correspond to the values in the dept_id column of the referenced table, which is department_detail.

• REFERENCES department_detail (dept_id):

Specifies the target of the foreign key relationship, indicating that the depart_id column in the employee_detail table refers to the dept_id column in the department_detail table. This establishes a link between the two tables, ensuring that each value in the depart_id column of employee_detail points to a valid dept_id in the department_detail table, maintaining referential integrity.

Dropping a Foreign Key Constraint

ALTER TABLE employee_detail  
DROP CONSTRAINT fk_department_id;

This SQL code is an alteration statement using the ALTER TABLE command to modify the structure of the employee_detail table. Specifically, it is removing (dropping) a constraint from the table.

• ALTER TABLE employee_detail:

  Specifies that the modification is applied to the employee_detail table.

• DROP CONSTRAINT fk_department_id:

  Instructs the database to remove the constraint named fk_department_id from the employee_detail table. The fk_department_id is the user-defined name of the constraint being dropped.