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.

Outlines

00:00

🔗 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.

05:00

📚 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.

Keywords

💡Foreign Key

A foreign key is an attribute in a database table that refers to the primary key in the same or another table. It is used to establish a link between two tables, ensuring data integrity and relational connections. In the script, the foreign key is exemplified by the 'roll number' in the 'course' table, which references the 'roll number' primary key in the 'student' table. This relationship allows for the association of courses with students, maintaining referential integrity.

💡Primary Key

A primary key is a unique identifier for each record in a database table. It cannot contain null values and ensures that each entry is distinct. In the context of the video, the 'roll number' is established as the primary key in the 'student' table, serving as a unique identifier for each student. This is crucial for the foreign key in the 'course' table to reference, ensuring that only valid student roll numbers can be associated with courses.

💡Referential Integrity

Referential integrity is a database constraint that ensures that all foreign key values must match values in the referenced primary key table. It is maintained by foreign keys and prevents data anomalies such as orphaned records. The script discusses how foreign keys, like the 'roll number' in the 'course' table, maintain referential integrity by ensuring that any value in the foreign key column must exist in the primary key column of the referenced table.

💡Composite Key

A composite key is a primary key composed of two or more attributes. It is used when a single attribute is not sufficient to uniquely identify a record. The script mentions that a primary key can be simple, consisting of one attribute, or composite, consisting of multiple attributes. This concept is important for understanding the flexibility of primary key design and how it can be referenced by foreign keys.

💡Referencing Table

The referencing table is the table that contains a foreign key. It is the table that relies on another table's primary key for establishing a relationship. In the script, the 'course' table is the referencing table because it contains the 'roll number' foreign key that references the 'student' table's primary key. This term helps to clarify the direction of the relationship between tables.

💡Referenced Table

The referenced table is the one that contains the primary key being referenced by a foreign key in another table. It is the source of the unique identifier for the relationship. In the video script, the 'student' table is the referenced table because its 'roll number' primary key is referenced by the foreign key in the 'course' table.

💡Data Type

Data type refers to the classification of data items, such as integer, varchar, etc., which determines the type of values that can be stored in a column. The script emphasizes that the data type of a foreign key must match the data type of the primary key it references, ensuring consistency and compatibility in data storage and retrieval.

💡Constraint

A constraint in database design is a rule that defines the limitations on data in a table. Foreign keys are a type of constraint that enforces referential integrity. The script describes how to define a foreign key constraint in SQL, using 'ALTER TABLE' to add a constraint after the table has been created, which is essential for maintaining the accuracy and consistency of the database.

💡Simple Key

A simple key is a primary key that consists of a single attribute. It is used when one attribute is sufficient to uniquely identify each record in a table. The script contrasts simple keys with composite keys, noting that the 'roll number' in the 'student' table is a simple key because it uniquely identifies each student without the need for additional attributes.

💡Null Value

A null value in a database represents the absence of a value or a lack of information. The script explains that primary keys cannot contain null values, which is crucial for ensuring that each record can be uniquely identified. This concept is important for understanding data integrity and the role of primary keys in database design.

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.