Lec-10: Foreign Key in DBMS | Full Concept with examples | DBMS in Hindi

Summary

In this educational video titled 'Gate Smashers,' the presenter delves into the concept of foreign keys in databases. The video is split into two segments: the first explains what a foreign key is, emphasizing its role as an attribute that references a primary key within the same or another table. The second part promises to explore why foreign keys are essential, particularly in maintaining referential integrity. Using the example of 'student' and 'course' tables, the presenter illustrates how foreign keys ensure data consistency across related tables. The video also touches on how to create and apply foreign keys in Oracle, highlighting the importance of matching data types and referencing the correct primary keys.

Takeaways

• 🔑 A foreign key is an attribute or a set of attributes in a table that refers to the primary key of the same or another table.
• 🔍 The primary key can be simple, consisting of one attribute, or composite, made up of multiple attributes.
• 📚 Referential Integrity is maintained by foreign keys, ensuring that the data in the database remains consistent and accurate.
• 🎓 The foreign key must have the same data type as the primary key it references to ensure data consistency.
• 🏫 In the provided example, the 'student' table has a primary key 'roll number', and the 'course' table has a foreign key 'roll number' referencing the 'student' table.
• 🚫 You cannot insert a value into a foreign key column unless that value exists as a primary key in the referenced table.
• 🔗 A table can have more than one foreign key, but it can only have one primary key.
• 🛠️ Foreign keys are created during table creation using the 'CREATE TABLE' statement or can be added to an existing table using the 'ALTER TABLE' statement.
• 📝 The script explains how to define a foreign key in Oracle SQL, specifying the data type, the 'REFERENCES' keyword, and the primary key of the referenced table.
• 🔄 The concept of a 'referencing table' and a 'referenced table' is introduced, with the former containing the foreign key and the latter containing the primary key.

Q & A

What is a foreign key in a database?

A foreign key is an attribute or a set of attributes in a table that references the primary key of another table or the same table.

Can a primary key be composite, and how does it affect the foreign key?

Yes, a primary key can be composite, meaning it is made up of more than one attribute. A foreign key can reference a composite primary key as well.

How does a foreign key maintain referential integrity in a database?

A foreign key maintains referential integrity by ensuring that a value in the foreign key column must exist as a primary key value in the referenced table. This prevents invalid references.

What are the two types of tables involved in a foreign key relationship?

The two types of tables are the 'referenced table,' which contains the primary key, and the 'referencing table,' which contains the foreign key.

Can a table have more than one foreign key? What about primary keys?

Yes, a table can have more than one foreign key, but it can only have one primary key.

What is the importance of having a common attribute between two tables in a database?

A common attribute, like a foreign key, is crucial because it allows the two tables to be related or joined together, making it possible to query related data from both tables.

How do you define a foreign key while creating a table in Oracle SQL?

To define a foreign key while creating a table, you use the 'REFERENCES' keyword. For example: `CREATE TABLE Course (Roll_number INT REFERENCES Student(Roll_number));`.

What is the query to add a foreign key to an already created table?

To add a foreign key to an existing table, you use the `ALTER TABLE` command. For example: `ALTER TABLE Course ADD CONSTRAINT fk_roll_number FOREIGN KEY (Roll_number) REFERENCES Student(Roll_number);`.

Does the name of the foreign key attribute need to match the primary key attribute?

No, the names of the foreign key and primary key attributes do not need to match, as long as they reference the correct values.

Why do we make the primary key unique and not null?

A primary key is made unique and not null to ensure that each row in a table can be uniquely identified, and no empty values are allowed in the primary key column.