What is Primary Key?
A primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system. It cannot be a duplicate, meaning the same value should not appear more than once in the table. A table can not have more than one primary key. Primary key can be defined at the column or the table level. If you create a composite primary key, it should be defined at the table level.
What is Foreign Key?
Foreign key is a column that creates a relationship between two tables. The purpose of the Foreign key is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. Every relationship in the database should be supported by a foreign key.
Difference between Primary Key and Foreign Key
Here is the important difference between Primary key and Foreign key:
What is Database Relationship?
The database relationship is associations between one or more tables that are created using join statements. It is used to efficiently retrieve data from the database. There are primarily three types of relationships 1) One-to-One, 2) One-to-many, 3) Many-to-many.
Why use Primary Key?
Here are the cons/benefits of using primary key:
The main aim of the primary key is to identify each and every record in the database table. You can use a primary key when you do not allow someone to enter null values. If you delete or update records, the action you specified will be undertaken to make sure data integrity. Perform restrict operation to rejects delete or update operation for the parent table. Data are organized in a sequence of clustered index whenever you physically organize DBMS table.
Why use Foreign Key?
Here are the important reasons of using foreign key:
Foreign keys help you to migrate entities using a primary key from the parent table. A foreign key enables you to link two or more tables together. It makes your database data consistent. A foreign key can be used to match a column or combination of columns with primary key in a parent table. SQL foreign key constraint is used to make sure the referential integrity of the data parent to match values in the child table.
Example of Primary Key
Syntax: Below is the syntax of Primary Key: Here,
Table_Name is the name of the table you have to create. Column_Name is the name of the column having the primary key.
Example: In the above example, we have created a student table with columns like StudID, Roll No, First Name, Last Name, and Email. StudID is chosen as a primary key because it can uniquely identify other rows in the table.
Example of Foreign Key
Syntax: Below is the syntax of Foreign Key: Here,
The parameter Table Name indicates the name of the table that you are going to create. The parameters column1, column2… depicts the columns that need to be added to the table. Constraint denotes the name of constraint you are creating. References indicate a table with the primary key.
Example: In the above example, we have two tables, a teacher and a department in a school. However, there is no way to see which search works in which department. In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables. This concept is also known as Referential Integrity.
