What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

Introduction

A SQL Foreign key is an integral part of the relational database system that establishes relationships between tables and explains to you how data stored in different tables  is related. It strongly supports data integrity and avoids modification that could violate the data integrity rules. The foreign is an important part of the database design similar to the primary keys and both had the capability to control data updates in more interesting ways.

So, let us have a deeper look at the SQL Foreign key basics How it is different from the primary key, how to create foreign keys in SQL, how to add them, use them, etc. When you go into deep, you will get an idea what value it brings to the database design.

What is a Foreign Key in SQL?

A foreign key SQL could be a column or a set of columns that provides the link between data among two tables in the RDBMS. This would be a cross-reference platform between two tables because it references the primary key of the second table and maintains the link between tables. Most of the tables in RDBMS follow  the concept of a foreign key SQL, so this is necessary to understand the topic deeply if you want to become a successful SQL Developer. For a complex database system, it is necessary that data from multiple tables should be linked together in a logical way and maintain a proper relationship too.

Read: Database Filters-Getting pure data from a pool of data

After this, the concept of data integrity is also related to the Foreign Key. Remember that the implementation of foreign keys is generally more complex as compared to the Primary Keys. Data should always be added or removed carefully with foreign key columns otherwise the careless handling could break the relationship between the two. Take an example of tables with the name Customer and Order, the relationship can be managed between the two with the proper introduction of the Foreign key in the Order table that will refer to the ID in the Customer table. Obviously, Customer IDs are given in both tables.

For the order table, customer IDs would become the foreign key and it will refer to the primary key in the Customer table. Every time you are adding value to the order table then a possible number of Foreign key constraints should be satisfied. In this way, data integrity is maintained automatically with Foreign key theory. You must be confused between a primary key and the Foreign key here. Before we move ahead, let us first discuss how they are different from each other?

Read: Database Filters-Getting pure data from a pool of data

Difference between Primary Key and Foreign Key in SQL

Here are the few popular facts that will explain to you how primary keys and foreign keys are different in the SQL.

• We cannot define the primary key as NULL but the foreign key could be defined as NULL.
• The Primary key is always unique but the foreign key can be repeated based on the convenience.
• The Primary key has the capability to identify one record uniquely. At the same time, the foreign key is just the field in a table that can be used as the primary key to another table.
• The Primary key is always one but foreign keys could be many.
• The Primary key adds one clustered index automatically but the foreign key could not add or create an index either it is clustered or non-clustered. Here, you need to create the index manually with step by step defined procedure.

Once you are sure on the basic concepts, let us go a little bit technical and learn the following sections one by one –

1. How to set the foreign key in SQL?
2. How to use the foreign key in SQL?
3. How to create a foreign key in SQL?
4. How to add or drop the foreign key in SQL?

Let us discuss each of the questions one by one with detailed explanations and proper methods.

Read: All About SQL Joins and Subqueries

How to set the Foreign Key in SQL?

Here is a quick list of referral actions that are used frequently to set the Foreign Key in the SQL and results in successful implementation in the end –

• Cascade – If the rows for the parent table are removed then corresponding SQL foreign keys columns are also removed automatically then is named as the Cascade Delete.
• Set Null – If any referenced row in the parent table is modified or removed, then foreign key values in the referencing rows will be defined as NULL to maintain the referential integrity.
• Restrict – This is another popular referential action associated with the Foreign key SQL. The value in the parent table cannot be deleted or modified until it is referred by the foreign key in another table.
• No Action: This action is very much similar to the ‘Restrict’ action and it is applied when you are planning to alter one table.
• Triggers – You must be thinking what we mean by Trigger here exactly? Triggers are generally related to referential actions and they are similar to the user-defined triggers. In order to make the execution successful, the referential actions are sometimes replaced with user-defined triggers.
• Set Default – This is almost similar to the Set Null parameter. If any referenced row in the parent table is modified or removed, then foreign key values in the referencing rows will be defined as NULL to maintain the referential integrity.

Read: What is SQL Subquery? Types of Subqueries in SQL

How to use the Foreign Key in SQL?

This is always recommended using the Foreign key where there is a 1:M relationship. If there is this type of relationship that exists somewhere then you can use the Foreign key immediately. For the object-oriented databases or non-relational databases, relationships are stored as pointers or addresses within a table record that is linked with other associated records in different tables.

But in the case of RDBMS i.e. Relational Database System, pointers are not defined in the form of 1:M relationships. This would be a great idea joining two tables with a JOIN query and the values for the foreign key in the column for different tables are linked to the Primary key of one table. In case, the two values look similar then the query will return the record in the result set.

Till the time, we have discussed the SQL foreign key concept multiple times and its comparison with the primary key. But it can be quickly used along a candidate key too. The candidate key could be Primary based on the reason you identify to complete that task. You have to make sure that the primary key, candidate key, and the primary key have the same data types when they are used together to create a table. The name of attributes would be different and you could generate a report for different tables too.

Read: How to Create Table in SQL Server by SQL Query?

How to create a Foreign Key in SQL?

To create the Foreign Key in the SQL, we can use two popular techniques. First is the inline method and the other is the out-of-the-line method. Let us discuss both the techniques one by one to make things easier for you. Inline Method In the case of the inline method, the following command can be used to create a Foreign key SQL –

CREATE table_name ( Column_name data_type REFERENCE other_table_name (other_column_name) …. ); 

Here, in the syntax, you can see the keyword CREATE, then you have to give the name of the table and an open bracket too. This is the general form of syntax and necessary to understand when working on Foreign Keys. If you wanted to define some column as the Foreign key then don’t forget to add the word Reference in the end after defining the data type for the column and give the name of another table in the end.

Read: How To Use The Exists Operator In The SQL?

Here we have given the table where the Primary key is defined and it is linked to the other table further by defining the foreign key constraints. Once you have done this, continue adding values to the table as usual and the foreign key will be created as soon as you will run the command.

Read: What is the Substring Function in the SQL? Example of SQL Server Substring

Out-of-the-line Method

This is another popular method of creating a SQL foreign key where you can give a name to the key that makes it more popular than the previous one. When you know the name for your key, you can alter, edit, or remove it later quickly by using its name. In the case of the inline method, some random name is given to the key as per the guidelines of Oracle. Here, is the syntax of how to create a Foreign key SQL with the out-of-the-line method.

CREATE table_name( Column_name data_type, …. CONSTRAINT fk_tabl1_tabl2 FOREIGN KEY (this_table_column) REFERENCES other_table_name (other_column_name) );

It will start by declaring the name of the table and all the columns you need. However, columns are defined within brackets, add a keyword in between i.e. CONSTRAINT. It will tell you that a keyword has been defined. Now give a name to the constraint that suits the condition with maximum characters of length up to 30.

Read: Different Type of SQL Joins

How to add or Drop the Foreign Key in SQL?

As we know already, the Foreign Key is used to create a bridge between two tables. This is used by database programmers as one of the critical components of the database to join data dynamically from two tables. In the absence of a Foreign Key, if you wanted to create a report that is utilizing data from more than two tables, you should use more exhaustive coding in a Non-SQL programming format. If you are planning to extract data from multiple tables then it is quite complicated and cannot be completed with simple SQL joins only. You need some more interesting ideas as soon as the complexity of retrieval increases. For this purpose, you have to de-normalize the table first, so that data could be quickly accessed by end-users. But de-normalization could hurt the data integrity because you have to synchronize details manually for each data update.

“The foreign key is the solution that controls data updates along with the primary key. When it is used or added correctly, the foreign key ensures data integrity too.”

Here is the syntax how can you add a foreign key to the products –

ALTER TABLE products ADD FOREIGN KEY fk_vendor(vdr_id) REFERENCES vendors(vdr_id) ON DELETE NO ACTION ON UPDATE CASCADE; 

Here is the syntax how can you drop the foreign key in SQL –

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

Read: SSRS Pie Chart - Having a Slice of the Pie

Conclusion

With this discussion, you had enough idea of how to add, drop, create or use a Foreign key. Now you can quickly use this constraint with your database as per the requirements. Make sure that all standard guidelines are followed as given by the Oracle. For more details, you should join the SQL certification program at JanBask Training right away to become a skilled database developer or administrator.

FaceBook Twitter LinkedIn Pinterest Email