MySQL Constraint – FOREIGN KEY

Hello everyone in the previous article we learn about MySQL Constraint – PRIMARY KEY and in this article, we will learn FOREIGN KEY constraint with example. Let’s go.

MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/

What is the FOREIGN KEY?

MySQL FOREIGN KEY constraint uniquely identifies a row or record in another table. Foreign key constraint creates a link between two table’s matches the Primary key in one of the tables with a foreign key in the 2nd table. Foreign key also called reference key.

C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/ 

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

FOREIGN Key points to a column of another table and accordingly links the two tables.  A Foreign Key field of fields from one table called the child table and it refers to a column in another table called the parent table. On that table, the set of columns in the parent table is the primary key and the child table called the referencing table and the parent table called the referenced table.

Table 1 (employee)     

employee_details

eID

eFirst_Name

eLast_Name

eAddress

eAge

eSalary

               Table 2 (department)

                                                         

department_details

dID

dName

dTeam_Leader

eID

In the above two tables, Table 1 – employee field eID is primary key but in Table 2 – department field eID is a Foreign Key.

MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/

The syntax for creating FOREIGN KEY with CREATE table statement.           

               CREATE TABLE table_name(

                             column1_name  data type ()  PRIMARY KEY,

                             column2_name data type ()  NOT NULL,

                             column3_name data type ()  NOT NULL

                             FOREIGN KEY (column_name) REFERENCES Primary_key_table (column_name)

               );

C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/ 

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

Example

               CREATE TABLE department (

                             dID INT AUTO_INCREMENT PRIMARY KEY,

                             dName VARCHAR (255) NOT NULL,

                             dTeam_Leader VARCHAR (255) NOT NULL,

                             FOREIGN KEY (eID) REFERENCES employee (eID)

               );

MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/

You can add FOREIGN KEY constraint when the table is already created using the ALTER TABLE statement. Following is the syntax and example:

               Syntax

                             ALTER TABLE table_name

ADD FOREIGN KEY (column_name) REFERENCES Primary_key_table (column_name);

              

Example

                             ALTER TABLE Department

                             FOREIGN KEY (eID) REFERENCES employee (eID);

If you want to drop created a FOREIGN KEY constraint, then follow the steps:

               Syntax

                             ALTER TABLE table_name

                             DROP FOREIGN KEY constraint_name;

               Example

                             ALTER TABLE Department

DROP FOREIGN KEY FK_EmployeeDepartment;

C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/ 

Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/