SQL FOREIGN KEY
FREE
SQL FOREIGN KEY
A foreign key in sql is a column or a group of columns in a table whose values are referenced from a primary key in another table. A primary key in SQL uniquely identifies records in a table. However, you must have a primary key for using a foreign key. The table containing the primary key is known as the parent table, whereas the table which contains the foreign key is known as the child table. Therefore, a foreign key relates two tables in a database.
Foreign key example in sql
Let's say we have a table named student which contains the Names, Roll Numbers, and Percentages of the students in a University.
The table is as shown below-
| Roll No | Name | Percentage |
|---|---|---|
| 2019021001 | Shefali | 88 |
| 2019021002 | Atul | 92 |
| 2019021003 | Koyal | 87 |
| 2019021004 | Aditya | 94 |
| 2019021005 | Suraj | 95 |
The Roll No column acts as a Primary key in the student table. It uniquely identifies a particular row in the table. We have another table named Course which contains the Roll No and the Name of the Course in which a particular student is enrolled.
The Course table is as shown below-
| SNo | Roll No | Course | Duration |
|---|---|---|---|
| 1 | 2019021001 | CSE | 4 |
| 2 | 2019021002 | ECE | 6 |
| 3 | 2019021003 | ME | 3 |
| 4 | 2019021004 | CE | 2 |
| 5 | 2019021005 | CSE | 4 |
However, the Roll No column in the Course table is used as a foreign key that references the student table's primary key. Therefore, the Roll No column acts as a foreign key.
We apply foreign key constraints in SQL that are used to prevent actions that would destroy the links between the tables. These constraints do not even allow entering invalid data into a table.
SQL FOREIGN KEY on CREATE TABLE
The CREATE command creates a new table in a database. However, it is also used to create a Foreign Key in a new table. Let us see an example to define the foreign key using the create command.
MySQL:
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
SQL Server / Oracle / MS Access:
To enable the naming of a FOREIGN KEY constraint and to define a FOREIGN KEY constraint across multiple columns, utilize the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
SQL FOREIGN KEY on ALTER TABLE
The ALTER table command in SQL is used to alter the columns in an already existing table. However, if you have already created a table and now you want to add a foreign key constraint to it, then you can execute the following query for doing so.
MySQL / SQL Server / Oracle / MS Access:
To enable the naming of a FOREIGN KEY constraint and to define a FOREIGN KEY constraint across multiple columns, utilize the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
Turn Learning into Career Growth
DROP a FOREIGN KEY Constraint
Once you have created a foreign key constraint, you may sometime wish to drop it from the table. The DROP command is used to delete a table from a database. However, it can also be used to drop a foreign key constraint in a table.
MySQL:
SQL Server / Oracle / MS Access:
In the query, you need to provide the name of the table and the name of the foreign key which you want to delete. As a result of the above query, the DeptNo column from the Employee table will be deleted.
SQL FOREIGN KEY at Column Level
You can use the foreign key at the column level in which you can apply a foreign key constraint in a table without using the foreign key keyword.
You can execute the following query to use the foreign key at the column level.
MYSQL:
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Primary key vs Foreign key in SQL
| Difference | Primary Key | Foreign Key |
|---|---|---|
| Nullability | Cannot be null | Can be null |
| Uniqueness | Must be unique | Can be duplicated |
| Purpose | Identifies records uniquely within a table | Establishes relationships by referencing the primary key of another table |
| Quantity per table | Only one per table | Can have more than one per table |
| Indexing | Automatically creates a clustered index | Does not automatically create an index; manual creation may be required |
Conclusion
- Foreign key in SQL is used to create a link between two tables.
- It is a column that creates a relationship between the tables by taking a reference from another table in the same database using the primary key.
- The foreign key in sql may contain null and duplicate values.
- You can create a foreign key in sql on a new table as well as on an already created table using CREATE and ALTER commands in SQL, respectively.
- You can also drop the foreign key using the DROP command.
- Foreign keys can be applied at the column level, directly within the column definition, simplifying the table creation process.
- Understanding the distinctions between primary keys and foreign keys is crucial. While primary keys uniquely identify records within a table, foreign keys establish relationships between tables and can be nullable and duplicated.
- Foreign key in SQL plays a vital role in database design, ensuring data integrity and facilitating relational database management.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more