- SQL Server Blogs -

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

A Foreign key is an integral part of the relational database system that establishes relationships between tables and explains to you how data stored in the different table is related. It strongly supports the 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 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.

Topic to be covered in Foreign Key Blog

  1. What is Foreign Key in SQL?
  2. How a Foreign Key is Different from Primary Key?
  3. How to set Foreign Key in SQL?
  4. How to Use Foreign Key in SQL?
  5. How to Create Foreign Key in SQL?
  6. How to Add Foreign Key in SQL

What is a Foreign Key in SQL?

A foreign key 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 follows the concept of foreign key, so this is necessary to understand the topic deeply of you wanted to become a successful SQL Developer. For a complex database system, this is necessary that data from multiple tables should be linked together in a logical way and maintain a proper relationship too. SQL Server Curriculum After this, the concept of data integrity is also related to the Foreign Key. Remember that 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 the ID in the Customer table. Obviously, the 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 a 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 are they different from each other?

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 into 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 the foreign key in SQL?
  4. How to add or drop the foreign key in SQL?

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

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 –

Read: How to use SQL Count() Aggregate Function
  • Cascade – If the rows for the parent table are removed then corresponding 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. 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 the 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.

How to use the Foreign Key in SQL?

This is always recommended using the Foreign key where there is 1:M relationship exists. If there is this type of relationship 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. SQL Server quiz But in the case of RDMS 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 foreign key concept multiple time and its comparison with the primary key. But it can be quickly used along 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 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.

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 the things easier for you. Inline Method In case of the inline method, the following command can be used to create a Foreign key –

 

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.

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: Top 100 SQL Interview Questions and Answers

Out-of-the-line Method

This is another popular method of creating a 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 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 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.

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 table. 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. free SQL Server demo 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 –

Read: Step By Step SSAS Tutorial For Beginners

 

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;

With this discussion, you had enough idea of how to add, drop, create or use of 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.

SQL Tutorial Overview

    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews