SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL FOREIGN KEY

In this article, we will learn about the FOREIGN KEY constraints and how to define a FOREIGN KEY constraint to build the relationship between two tables.

In a Relational Databases Management System (RDBMS), a FOREIGN KEY is a field or bunch of fields which is used to build a relationship between two tables or join the two tables.

  • FOREIGN KEY is also known as referencing key in the RDBMS
  • Using FOREIGN KEY constraints between two tables, we define a parent-child relationship between two tables.
  • The field defines the PRIMARY KEY constraints in a table known as the parent table, while the same field defines the FOREIGN KEY constraints in another table is known as the child table.
  • There can be only one PRIMARY KEY in the table, but there is no defined limit on the FOREIGN KEY. We can have one or more than one FOREIGN KEY on the table.
  • We can define FOREIGN KEY constraints while creating the table or define FOREIGN KEY constraints on the already existing table.

Let’s take an example to understand and learn about the FOREIGN KEY constraints.

Example:

Here, we have two tables one is the Course table and the second is the Student table:

The students select courses. The following FOREIGN KEY constraints example is defined on one field.

Table Number One: Student Table

Student_IdFirst_NameLast_NameEmail_IdCity
101KareenaSayyed[email protected]Hyderabad
102AnmolTemani[email protected]Jalgaon
103HarshalPatel[email protected]Mumbai
104SakshiHood[email protected]Pune
105SureshKohli[email protected]Hyderabad

Table Number Two: Course Table

Course_IdCourse_NameStudent_Id
C101Java101
C102SQL102
C103SOANull
C104SAP104
C105MuleSoft103
C101Java105

In the Student table, the Student_Id field is the PRIMARY KEY and in the Course table, Student_Id is the FOREIGN KEY.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column because it has to be one of the values contained in the parent table.

Define FOREIGN KEY Constraints on CREATE TABLE

The following example defines a FOREIGN KEY on the Course table.

First, we have created the Student table with the following fields:

CREATE TABLE Student( Student_Id int NOT NULL, First_Name varchar(40) NOT NULL, Last_Name varchar(40) NOT NULL, Email_Id varchar(50),  City varchar(20) NOT NULL, PRIMARY KEY(Student_Id));

The following example shows how to define the FOREIGN KEY constraints on the other table.

Table Name Course:     

CREATE TABLE Course( Course_Id int NOT NULL, Course_Name varchar(40) NOT NULL, Student_Id int, FOREIGN KEY(Student_Id) REFERENCES Student(Student_Id));

In the Course table, we didn’t define any PRIMARY KEY. Just define the FOREIGN KEY constraints on the Student_Id.

The following FOREIGN KEY constraints example is defined on multiple fields.

Suppose we have three tables, one is the Package table, the second is the Data table, and the third is the Talk_time table:

Table 1: Package:

Package_IdData_IdTalktime_Id
P1001D1003T1001
P1002D1001T1002
P1003D1002T1003
P1004D1003T1004
P1005D1004T1005

Table 2: Data:

Data_IdData_LimitData_Price
D10015120
D1002375
D10036150
D100410240
D100515320

Table 3: Talk_Time:

Talk_Time_IdTalk_Time _LimitTalk_Time _Price
T1001120130
T100270105
T10036090
T1004200220
T1005150170

In the Talk_Time table, Talk_Time_Id is the PRIMARY KEY.

In the Data table, Data_Id is the PRIMARY KEY.

Whereas in the Package table, Talk_Time_Id and Data_Id are the FOREIGN keys

Table Number one: Data:

CREATE TABLE Data(Data_Id varchar(5) NOT NULL, Data_Limit int, Data_Price int, PRIMARY KEY(Data_Id));

Table Number two: Talk_Time:

CREATE TABLE Talk_Time(Talk_Time_Id varchar(5) NOT NULL, Talk_Time_Limit int, Talk_Time_Price int , PRIMARY KEY(Talk_Time_Id));

Table Number three: Package:

CREATE TABLE Package(Package_Id varchar(5) NOT NULL, Data_Id varchar(5), Talk_Time_Id varchar(5), FOREIGN KEY(Data_Id) REFERENCES Data(Data_Id), FOREIGN KEY(Talk_Time_Id) REFERENCES Talk_Time(Talk_Time_Id));

FOREIGN KEY Constraints using ALTER TABLE:

Suppose we have already created the table and want to define the FOREIGN KEY constraints on the field. We will use the ALTER TABLE query to add FOREIGN KEY constraints in such a case.

The follow query is used to add FOREIGN KEY constraints on the Student_Id field.

ALTER TABLE Course ADD FOREIGN KEY(Student_Id) REFERENCES Student(Student_Id);

ADD Keyword is used after the table name to add the FOREIGN KEY constraints to the already existing table.

DROP FOREIGN KEY constraint from the table

Use the following query to remove the FOREIGN KEY constraint from the table.

ALTER TABLE Course DROP FOREIGN KEY course_ibfk_1;

Drop keyword is used to remove FOREIGN KEY constraints from the Student_Id field.

course_ibfk_1 is the foreign key constraint name.

We can have null values in the FOREIGN KEY constraints field. We can have duplicate values in the FOREIGN KEY constraints field.



ADVERTISEMENT
ADVERTISEMENT