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/


Comments

Post a Comment

Popular posts from this blog

ALTER statement in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the ALTER table statement in MySQL. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ 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/ MySQL ALTER command is used to modify the table, view an existing database. When you have completed your database design and after that, you realize some of the information was left out when creating a database. You want to change without losing existing data, and then you can change data using ALTER command. MySQL ALTER command is used to add a column with an existing table, add multiple columns, drop a column, modify a column, rename a column, and rename the table. ALTER command also used to add and drop constraints on the existing table. ...
Read more

Order By Clause in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the Order By Clause in MySQL. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ 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/ MySQL Order by clause is used to sort the records or data in ascending or descending order. Order by clause allows us to sort records or data by a single column or multiple columns in ascending or descending order. There are two key using in Order by clause:          ASC: - sorting data or records by ascending order.          DESC: - sorting data or records by descending order. Syntax:          SELECT column_name_1, column_n...
Read more

MySQL Constraint – PRIMARY KEY

Image
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/ Hello everyone in the previous article we learn about MySQL Constraint – UNIQUE and in this article, we will learn PRIMARY 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 PRIMARY KEY? MySQL PRIMARY KEY constraint uniquely identifies each row in a table and also it creates a unique index for accessing the table will access faster. The columns of a Primary Key cannot have null values in MySQL and it must contain unique values. A Primary key has only one on a table, if the primary key used with multiple fields, then they are called a Composite Key. Syntax 1 for creating PRIMARY KEY with CREATE table statement.      ...
Read more