Introduction to DBMS Foreign Key
A key that is used to link two tables together or create connectivity between two tables is called a Foreign key. It is different from the primary key as a foreign key should be present in both the tables, unlike the primary key. For example, consider two tables having the primary key of its own and another key in the first table is present in the second table as primary key, then the key in the first table is called the foreign key. It has nothing to do with primary keys, but it surely points out which keys should be considered as the primary key. In this topic, we are going to learn about DBMS Foreign Key.
Foreign key vs Composite key
- The primary key or composite key is used to verify whether the data in a table is unique or not and is made by the combination of more than one column in a table, whereas a foreign key is used to link two tables that can be unique or not. A composite key is unique in a table, and there cannot be any repeating columns in the same table. But if we are taking individual columns, it doesn’t need to be unique in the table. The foreign key represents data in a table which is a unique key in another table, and hence it represents the composite key of another table.
- We can have only one composite key in a table as it uniquely identifies the table itself. However, we can have more than one foreign key in a table, and we can link one table with more than two other tables. Also, the composite key has constraints of not null and unique, whereas the foreign key can have duplicate values in the table.
- The composite key does not allow null values in the table as it denotes the unique values of a column, whereas the foreign key allows null values in the table. Once the composite key is defined, it is not possible to delete the values from the table. But we can delete foreign keys from a table as there are no constraints for the same. We can define the composite key constraints in temporary tables, but the foreign key constraints cannot be defined in any other local or global temporary tables.
How did Foreign keys work?
- We should note that foreign keys cannot be computed from any of the columns, and the type must be similar to the referenced column. For example, if we use a primary key to reference the foreign key and the type of primary key is INT, we should use the INT type in the foreign key. Also, referenced columns must always be the primary key, as they should be unique in the table. Finally, the foreign key should follow referential integrity constraints within the table. The foreign key should be present in the parent table as the primary key or there as a null value. This rule must be followed, or else there can be database violations, and hence database systems themselves ensure that foreign key corresponds to the primary row in a table.
- Foreign keys are essential in database design and database normalization. It is important to have references to other tables so that if the real-world entities are referenced in the table, we can see the connections in the table by references in the database. This helps to manage the database easily via database connections. Also, during normalization, if the tables and data are present in different places, it is important to know the connections between tables, and this is achieved with the help of foreign keys. Foreign keys help to restructure the entire tables by giving all the references to the parent tables. The table with primary keys is called the parent table, and the table with foreign keys is called child tables. We can have one too many relationships between parent and child table when different rows in the child table refer to the single row in the parent table.
- Another point to note is that we may have both parent and child tables in the same table. This is called self-referencing or recursive foreign key, where the foreign key refers to the same parent table. This is done by linking all the foreign keys references to the same table. It is not necessary that a table can have only one foreign key, and these multiple foreign keys in a single table may refer to various parent tables. Database management system enforces the foreign keys differently to various tables, and hence the cascading relationships are established using these keys in the table. Therefore, it is important to denote foreign keys in the syntax to make sure that key is taken as the foreign key in the table.
For example, DBMS Foreign key.
Consider two tables employees and organization as given below.
Looking at both the tables, we can see that Emp_ID is common in the Employees and Organization table. In the Employees table, we have Emp_ID as the table’s primary key, whereas, in the Organization table, we have Emp_ID as the foreign key. Emp_ID is linking both the tables and acts as a foreign key and primary key to the tables. This query below helps in creating a foreign key attributing the Employee’s table reference.
CREATE TABLE Organization (
Org_Name varchar (100) NOT NULL,
Emp_ID INT,
FOREIGN KEY (Emp_ID) REFERENCES Employees (Emp_ID)
);
We can alter the table by adding the foreign key or drop the foreign key attribute from the table. Point to be noted that we should be careful while dropping foreign key attributes as they might be linked with other tables. If it disturbs the integrity of the second table, it will throw an error while running the drop query.
ALTER TABLE Organization
ADD FOREIGN KEY (Emp_ID) REFERENCES Employees (Emp_ID);
ALTER TABLE Organization
DROP FOREIGN KEY FK_EmployeesOrganization;
Conclusion
It is important to know about foreign key and the usage of it in different tables. This helps us to manage the queries by enabling the constraints concerning the primary keys. For example, we can give SET DEFAULT to foreign keys so that they will not be deleted unknowingly by any users.
Recommended Articles
This is a guide to DBMS Foreign Key. Here we discuss how foreign keys work and the Difference between the foreign and composite keys. You may also have a look at the following articles to learn more –
