EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DBMS Tutorial DBMS Foreign Key
Secondary Sidebar
DBMS Tutorial
  • DBMS
    • What is DBMS?
    • Introduction To DBMS
    • DBMS ER Diagram
    • DBMS_RANDOM
    • DBMS_XPLAN
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • Checkpoint in DBMS
    • DBMS Table
    • Mapping Constraints in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • View Serializability in DBMS
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

DBMS Foreign Key

DBMS Foreign Key

Introduction to DBMS Foreign Key

A key that is used to link two tables together or create connectivity between two tables is called a Foreign key. It is different from the primary key as a foreign key should be present in both the tables, unlike the primary key. For example, consider two tables having the primary key of its own and another key in the first table is present in the second table as primary key, then the key in the first table is called the foreign key. It has nothing to do with primary keys, but it surely points out which keys should be considered as the primary key. In this topic, we are going to learn about DBMS Foreign Key.

Foreign key vs Composite key

  1. The primary key or composite key is used to verify whether the data in a table is unique or not and is made by the combination of more than one column in a table, whereas a foreign key is used to link two tables that can be unique or not. A composite key is unique in a table, and there cannot be any repeating columns in the same table. But if we are taking individual columns, it doesn’t need to be unique in the table. The foreign key represents data in a table which is a unique key in another table, and hence it represents the composite key of another table.
  2. We can have only one composite key in a table as it uniquely identifies the table itself. However, we can have more than one foreign key in a table, and we can link one table with more than two other tables. Also, the composite key has constraints of not null and unique, whereas the foreign key can have duplicate values in the table.
  3. The composite key does not allow null values in the table as it denotes the unique values of a column, whereas the foreign key allows null values in the table. Once the composite key is defined, it is not possible to delete the values from the table. But we can delete foreign keys from a table as there are no constraints for the same. We can define the composite key constraints in temporary tables, but the foreign key constraints cannot be defined in any other local or global temporary tables.

How did Foreign keys work?

  • We should note that foreign keys cannot be computed from any of the columns, and the type must be similar to the referenced column. For example, if we use a primary key to reference the foreign key and the type of primary key is INT, we should use the INT type in the foreign key. Also, referenced columns must always be the primary key, as they should be unique in the table. Finally, the foreign key should follow referential integrity constraints within the table. The foreign key should be present in the parent table as the primary key or there as a null value. This rule must be followed, or else there can be database violations, and hence database systems themselves ensure that foreign key corresponds to the primary row in a table.
  • Foreign keys are essential in database design and database normalization. It is important to have references to other tables so that if the real-world entities are referenced in the table, we can see the connections in the table by references in the database. This helps to manage the database easily via database connections. Also, during normalization, if the tables and data are present in different places, it is important to know the connections between tables, and this is achieved with the help of foreign keys. Foreign keys help to restructure the entire tables by giving all the references to the parent tables. The table with primary keys is called the parent table, and the table with foreign keys is called child tables. We can have one too many relationships between parent and child table when different rows in the child table refer to the single row in the parent table.
  • Another point to note is that we may have both parent and child tables in the same table. This is called self-referencing or recursive foreign key, where the foreign key refers to the same parent table. This is done by linking all the foreign keys references to the same table. It is not necessary that a table can have only one foreign key, and these multiple foreign keys in a single table may refer to various parent tables. Database management system enforces the foreign keys differently to various tables, and hence the cascading relationships are established using these keys in the table. Therefore, it is important to denote foreign keys in the syntax to make sure that key is taken as the foreign key in the table.

For example, DBMS Foreign key.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Consider two tables employees and organization as given below.

DBMS Foreign Key output 1

DBMS Foreign Key output 2

Looking at both the tables, we can see that Emp_ID is common in the Employees and Organization table. In the Employees table, we have Emp_ID as the table’s primary key, whereas, in the Organization table, we have Emp_ID as the foreign key. Emp_ID is linking both the tables and acts as a foreign key and primary key to the tables. This query below helps in creating a foreign key attributing the Employee’s table reference.

CREATE TABLE Organization (
Org_Name varchar (100) NOT NULL,
Emp_ID INT,
FOREIGN KEY (Emp_ID) REFERENCES Employees (Emp_ID)
);

We can alter the table by adding the foreign key or drop the foreign key attribute from the table. Point to be noted that we should be careful while dropping foreign key attributes as they might be linked with other tables. If it disturbs the integrity of the second table, it will throw an error while running the drop query.

ALTER TABLE Organization
ADD FOREIGN KEY (Emp_ID) REFERENCES Employees (Emp_ID);
ALTER TABLE Organization
DROP FOREIGN KEY FK_EmployeesOrganization;

Conclusion

It is important to know about foreign key and the usage of it in different tables. This helps us to manage the queries by enabling the constraints concerning the primary keys. For example, we can give SET DEFAULT to foreign keys so that they will not be deleted unknowingly by any users.

Recommended Articles

This is a guide to DBMS Foreign Key. Here we discuss how foreign keys work and the Difference between the foreign and composite keys. You may also have a look at the following articles to learn more –

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)
  1. DBMS Locks
  2. DBMS_Scheduler
  3. DBMS Multivalued Dependency
  4. Checkpoint in DBMS
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more