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/
