Lec-10: Foreign Key in DBMS | Full Concept with examples | DBMS in Hindi
Summary
TLDRIn 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.
Outlines
🔗 Introduction to Foreign Keys in Databases
This paragraph introduces the topic of foreign keys in databases. The speaker begins by welcoming viewers to the 'Gate Smashers' channel and requests them to subscribe and enable notifications for updates. The video is divided into two parts: the first part defines what a foreign key is, and the second part discusses its purpose in databases. A foreign key is described as an attribute or a set of attributes that references the primary key of the same or another table. The concept of primary keys being either simple or composite is explained, emphasizing that foreign keys must reference primary keys, which can be found in the same table or in a different one. The paragraph also touches on the importance of referential integrity and how foreign keys play a role in maintaining it, a topic that will be further explored in the second part of the video. Two example tables, 'student' and 'course', are introduced to illustrate the relationship between tables using foreign keys.
📚 Implementing Foreign Keys and Referential Integrity
In this paragraph, the speaker delves into the implementation of foreign keys and the concept of referential integrity. It explains that the 'student' table is the base table with a primary key 'roll number', and the 'course' table includes a 'roll number' column that acts as a foreign key referencing the 'student' table. The importance of matching data types between the foreign key and the primary key is highlighted, as well as the syntax for creating a foreign key in Oracle. The speaker also discusses how to add a foreign key constraint to an existing table using the 'ALTER TABLE' command. The paragraph concludes by clarifying that the names of the columns used as foreign keys do not have to match the primary key column names, and that a table can have multiple foreign keys, unlike primary keys, which are unique to a table. The speaker promises to cover the maintenance of referential integrity in both referenced and referencing tables in the next part of the video.
Mindmap
Keywords
💡Foreign Key
💡Primary Key
💡Referential Integrity
💡Composite Key
💡Referencing Table
💡Referenced Table
💡Data Type
💡Constraint
💡Simple Key
💡Null Value
Highlights
Introduction to the concept of foreign keys in databases.
Definition of a foreign key as an attribute that references a primary key within the same or another table.
Explanation of primary keys being simple or composite.
Discussion on the importance of primary keys for unique identification in databases.
The role of foreign keys in maintaining referential integrity.
How foreign keys reference primary keys either in the same table or a different one.
Illustration of foreign key usage with an example of 'student' and 'course' tables.
Clarification that the roll number in the 'course' table is a foreign key referencing the 'student' table.
The requirement for the data type of a foreign key to match the data type of the referenced primary key.
Instructions on how to create a foreign key in Oracle using SQL commands.
The difference between referencing and referenced tables in the context of foreign keys.
Demonstration of altering an existing table to add a foreign key constraint.
Highlight that the name of the foreign key column does not have to match the primary key column.
Revelation that a table can have multiple foreign keys, unlike the single primary key constraint.
Anticipation of the second part of the video, which will cover referential integrity in more depth.
Transcripts
Hello Friends
Welcome to Gate Smashers
The topic is foreign key in database.
So, in this video we are going to discuss about foreign key
Where what is the foreign key?
And why it is actually used in the database?
So before starting this video,
I want to request from my viewers,
Please subscribe to my channel.
And please press the bell button,
so that you get the latest notifications.
So here actually I have divided the video in two parts,
In part one we are going to discuss
What is a foreign key?
In Part Two, why it is actually used in database?
So, what is foreign key,
It is an attribute of set of attributes
that references to Primary key of same table.
or another table. Table means the relation.
Means first of all, you focus on this point
"it's attribute of set of attributes".
Means to say.
that primary key
Can be simple or it can be composite.
Means if there is a primary key in a table.
So that primary key made up of the only one attribute.
Or a primary key consisting of
two or three attributes.
So it actually means
that primary key is a simple or whether it is a composite
But the Main Point is
That the primary key always reference,
It take references from the primary key of
Same table
or another table, means
Whenever Foreign key take a reference,
whenever takes their data,
From where it takes the reference,
from the primary key
and in which table the primary will be?
Either in the same table.
Or it is existing in another table
So here the important point is this.
Many times in the interviews also and often comes in the exams.
That what is the Referential Integrity
and which keys used to maintain the referential Integrity,
It is foreign key.
What is the concept of this referential integrity?
And how Foreign key maintain it?
We'll see this in Part Two.
Here we are going to see first,
what is actually a foreign key?
So here i have taken two tables.
There's a table here, student.
And the second table I took here.
course.
So in the student table
Let's say it is a base table.
It is the base table.
Suppose, it is a base table of any university or college
where the Data of all student are present,
where student's roll number
Student's name
Student's address are present.
There's a lot of entries and rows.
And here I have made roll number
as primary key.
Why do we make the primary key?
It is unique as well as not null,
means neither I can leave it empty
nor can I repeat any value
and it's benefit I have already explained.
Ans its benefit is
that in any university,
if the value of any number of rows same
If I have to uniquely identify them.
Whose help do we always take?
Primary key.
Means the value of all the columns can be same
But there will be a column in which
there is a difference between every two.
Why that?
Because one is the unique value in it.
& empty means you can't even leave it null.
I already explain the concept of primary key
Here we talk about, the second table is the course.
In the course, we took the course ID,
took the course name and took a attribute here, Roll number.
Now here why have I taken the roll number because
The student & course are related to each other
Whenever you feel that
these two tables are related to each other,
how do we denote them?
There will be aleast one attribute must be
common between them.
so that in future, I can join these two tables.
what do I have in common in these two tables?
Roll number.
You can also keep the course ID here,
that's not a problem.
But I've kept the roll number here.
And this roll number
working here as a Foreign key.
And this foreign key,
where it is taking reference from?
It's taking a reference from
this primary key.
What does it mean to take a reference?
What is the meaning of the reference
that here in this table
Like new students are coming
New students are taking admission here,
and I'm putting their data.
New courses are coming.
Their course name like course 1, course 2, course 3, course 4.
There is a lot of data, that too, I'm entering it
But whenever I have to put data in this column.
I have to put very carefully,
because where is the value of this column being referenced from?
From here on,
if I ask a simple question, Can I write
the Roll number 10?
No,
Because roll number 10 should be in this table first.
If it exist in this table.
then obviously roll numbers 10 can be in this table as well.
because where are this tables taking the referencing from?
From this table,
where are this foreign table takimg reference from?.
From the primary key of this table,
so we called this table,
Referencing in tables. Referencing.
And we called this table
Referenced.
Referenced Table,
means the table in which primary key,
We call it the referenced table or the base table.
and in the table,
in which foreign key exist,
we call ot referencing table,
who is taking the reference in the table.
And I have denoted here that
how we make foreign key in the oracle.
If in the oracle or
normally in a sequel, if I'm writing a command.
And I'm using my platform of oracle.
Create Table Course.
We are making this tables
in which we took the course ID, let say varchar
We also took the course name Varchar
The main point here is the roll number,
I have to make the roll number as a foreign key
So roll number, integer,
this data type should be equal to
the data type of the primary key,
The data type of the primary key is used here,
the same data type is to be used here,
then write references.
After references, table of primary key,
that table name where primary key exist.
Which table is that, student.
And in the bracket put primary key,
which primary key is in that table?
The roll number.
So we used this thing here.
I represented it this case, because in case,
If there is a coding, let say like
if a query is written,
then you can also note it down like this.
And if already table is made
This is when we are making the table,
during the creation.
How can we apply foreign key?
If after the table created
means the table has already made
& now I have to put the foreign key.
So the query for that is Alter
Table.
Let say, take course
Alter, Table, Table Name, Course.
Add
Constraints
Given the Name to Constraint, let's say FK
Name anything,
Actually foreign key is a constraints.
there is a rule.
Add, Constraint, foreign key, FK
Which constraint is this?
Foreign key,
Foreign key,
And who are you creating the foreign key?
Roll number of that table
Who are you making of that table?
Roll number
and where will it take the reference from?
It will take the reference.
Reference.
References.
What is the name of the primary table, student.
And in that student, which is the primary key?
Roll number. So this is how,
we write a query.
After the table is created. Alter, Table,
Table Name, Add, Constraint, constraint name, foreign key,
Which you have to make roll number,
references- student, the table of primary key.
And what is the primary key in that table.
Now let me tell you a small point here,
that in both these tables I took the roll number, roll number.
It doesn't mean that their name should be same.
you take some other names here.
That is not a problem & the second point is
Can a table have more than one foreign key?
Yes, a table have more than one foreign key.
but primary key is always one in a table.
So this is all about the part one,
where what is foreign key,
we have discussed.
In part two, we will talk about
the referential integrity,
that how foreign key maintain
Referential integrity
in the referenced table as well as
in the referencing table?
Thank you.
Посмотреть больше похожих видео
5.0 / 5 (0 votes)