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.
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. 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?
Here are the few popular facts that will explain to you how primary keys and foreign keys are different in the SQL.
Once you are sure on the basic concepts, let us go a little bit technical and learn the following sections one by one –
Let us discuss on each of the questions one by one with detailed explanations and proper methods.
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 –
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. 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.
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.
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.
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. 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;
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.
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.
Course for testing
Receive Latest Materials and Offers on SQL Server Course