Short Answer
A primary key is a field in a database table that uniquely identifies each record. It ensures that no two rows have the same value and cannot be left empty. It is used to maintain uniqueness in a table.
A foreign key is a field in one table that refers to the primary key of another table. It is used to create a relationship between two tables and helps in linking related data in a database system.
Detailed Explanation:
Primary Key Foreign Key
Introduction
In a relational database system, keys are very important for organizing and managing data properly. Two main types of keys used are primary key and foreign key. These keys help in identifying records and creating relationships between different tables.
They are widely used in database management systems to ensure data accuracy, uniqueness, and proper linking between tables. Without these keys, database systems would become disorganized and difficult to manage.
Primary Key
Definition of Primary Key
A primary key is a column or a set of columns in a database table that uniquely identifies each record in that table. It ensures that every row has a unique identity.
A primary key cannot contain duplicate values and cannot have NULL values. This makes it very important for maintaining data integrity in a database.
Features of Primary Key
The primary key has some important features that make it useful in database design.
It always contains unique values, so no two records are the same. It does not allow NULL values because every record must have an identity.
A table can have only one primary key, but it can consist of one or more columns combined together. This is called a composite primary key.
Example of Primary Key
In a Student table, Roll Number can be used as a primary key because every student has a unique roll number.
For example:
- Roll No: 101, Name: Ravi
- Roll No: 102, Name: Sita
Here, Roll No uniquely identifies each student.
Foreign Key
Definition of Foreign Key
A foreign key is a field in one table that refers to the primary key of another table. It is used to create a relationship between two tables in a database.
It helps in connecting data from different tables and maintaining consistency between them.
Features of Foreign Key
A foreign key can contain duplicate values because multiple records can refer to the same record in another table.
It can also contain NULL values depending on the design. A table can have more than one foreign key.
Foreign keys help in maintaining referential integrity, which means relationships between tables remain consistent.
Example of Foreign Key
Consider two tables: Student and Course.
In the Student table, Course_ID can be a foreign key that refers to the Course table’s primary key.
For example:
- Course Table: Course_ID = 1, Course Name = DBMS
- Student Table: Course_ID = 1, Student Name = Amit
Here, Course_ID in Student table links to Course_ID in Course table.
Difference Between Primary Key and Foreign Key
Primary key and foreign key are both important in relational databases but serve different purposes.
A primary key is used to uniquely identify records within the same table, while a foreign key is used to connect two different tables.
Primary key values must be unique and not null, whereas foreign key values can be repeated and may contain null values.
A table can have only one primary key, but it can have multiple foreign keys depending on relationships.
Importance in Database System
Primary keys and foreign keys are very important in database design because they help in organizing data properly.
Primary keys ensure that each record is unique and easily identifiable. Foreign keys help in linking related data from different tables.
Together, they maintain data integrity and consistency in relational databases.
For example, in a college database, Student ID (primary key) identifies each student, while Course ID (foreign key) connects students to their courses.
Conclusion
Primary key and foreign key are essential components of relational databases. A primary key uniquely identifies each record in a table, while a foreign key creates a relationship between two tables. Together, they ensure data uniqueness, consistency, and proper organization in database systems.