Difference between Primary Key and Foreign Key in MySQL




Hello and welcome everyone. In this article, we will learn about the difference between Primary Key and Foreign Key. Let’s go.
Primary Key and Foreign Key

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 PRIMARY KEY constraint uniquely identifies each row in a table.
MySQL FOREIGN KEY constraint uniquely identifies a row or record in another table.

              MySQL PRIMARY KEY select candidate key, therefore, it does uniquely define the relation.
MySQL FOREIGN KEY current table refers to the primary key of another table.

              MySQL PRIMARY KEY NULL values are not accepted.
MySQL FOREIGN KEY accepted multiple NULL values.

              MySQL PRIMARY KEY automatically create a clustered index.
MySQL FOREIGN KEY clustered index or non-clustered index we need to create manually.

              MySQL PRIMARY KEY is the parent table on the same column in the table.
MySQL FOREIGN KEY is child table on the same column in the table.

              MySQL PRIMARY KEY has only one in a table.
MySQL FOREIGN KEY has more than one in a table.

              MySQL PRIMARY KEY used with CREATE statement.
MySQL FOREIGN KEY also used with CREATE statement.

              MySQL PRIMARY KEY you can’t insert duplicates values.
MySQL FOREIGN KEY you can insert duplicates values.

              MySQL PRIMARY KEY value can be referring to another table as a FOREIGN KEY.
MySQL FOREIGN KEY value has only a foreign key.

              MySQL PRIMARY KEY defines on the temporary table.
MySQL FOREIGN KEY can’t define on the temporary table.

              MySQL PRIMARY KEY didn’t want to reference when insert values in the primary key.
MySQL FOREIGN KEY referenced primary key column to insert value.

              MySQL PRIMARY KEY working on only that table.
MySQL FOREIGN KEY is working on two tables and also it linking two tables using INNER JOIN and OUTER JOIN.

              MySQL PRIMARY KEY supports to AUTO INCREMENT field.
MySQL FOREIGN KEY does not support to AUTO INCREMENT field.

              The syntax for PRIMARY KEY
                             CREATE TABLE table_name (
                                            column1_name  data type ()  PRIMARY KEY,
                                            column2_name data type ()  NOT NULL,
                                            .
                                            .             
                              );


Syntax for FOREIGN KEY
               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)
               );



               Example of PRIMARY KEY
                             CREATE TABLE EMPLOYEE (
                                            eID INT PRIMARY KEY AUTO_INCREMENT,
                                            eNAME VARCHAR (255)            NOT NULL,
                                            eAGE INT NOT NULL,
                                            eADDRESS VARCHAR (255) NOT NULL
                              );


Example of FOREIGN KEY
               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)
               );

In this article, we learned the difference between PRIMARY KEY and FOREIGN KEY with their syntax and example.


Visit Our Website : https://expertwithsagarjaybhay.com/


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

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

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