- CREATE table query
- UPDATE/ALTER query
[ON DELETE action]
[ON UPDATE action]
The reference table is the parent table. And the column for the foreign key is the primary key of the parent.
Foreign key introducing in CREATE statement
As for foreign key constraints, we need two tables to illustrate the concept. Let us begin with defining a table “designer,” and the second one is “category”. Both tables are created by using a create statement.
In the table “designer”, designer_id is set as a primary key. Now create the second table.
In this table, category_id is set as a primary key. As both these tables are to join with the foreign key constraint. We make the “designer_id” the foreign key in this table. The reference table is mentioned in the table so that the query can be easily executed by searching the table.
The designer table is the PARENT table, whereas the “category” table is the CHILD table. Each designer works on zero or more dress categories, and one or more designers consider each dress category.
NOTE: To make a foreign key of the id of one table in the second table, it is important to make that specific id as a PRIMARY KEY in its table. Otherwise, it will not form a foreign key in the other table. An error will occur while creating the foreign key.
After the creation of tables, we use to enter values in it by “insert” command.
Similarly in the case of second table, enter the values.
In the “category” table, the values will be inserted in two columns including category_name, and designer_id. We have added the same number for the id and the same name for the category_name here to fulfill the condition. Now apply the delete command to prove the “no action” type of the foreign key. In this command, we have provided an id number to delete the specific row from the table.
This query will not execute successfully. It will show an error message as displayed in the snap as mentioned above. This error is because of “NO DELETE NO ACTION”. Postgresql shows the constraint violation because the designer_id = 1 is for two rows currently present in the table.
SET NULL as option ON DELETE clause
Create a table named “employee” having emp_id as a PRIMARY KEY.
When we execute the query, a message is displayed that shows that our query is executed successfully. After creating the employee table, create its child table “info” by using “emp_id” as the foreign key.
Execute the query, and you will see that the table is created. Like the “create” table statement used before, this is the same. Here we have added property.
“ON DELETE SET NULL”. This action is applied in the ON DELETE clause. Now we have to fill the tables with the values.
The delete command consists of the “where” clause to identify the id to be deleted from the row in the table employee.
The relevant data will be deleted on the execution of the query. We will see the resultant table left after that query.
The rows having the specific reference in the child table ‘info’ are set to NULL because we used the ON DELETE SET NULL action in the command at the table creation.
Using an ALTER table command
In the previous examples, we have seen how to apply the constraints at the time of the creation of the tables. What if you have created the tables with FK (foreign key) constraints, and later on, you want to add constraints? Here is a solution to this question. Firstly we will create two tables. But at the time of creating the table, there is no need to identify the foreign key in the second table to make it as a child.
This table does not contain the foreign key constraints and references, etc. After insertion, we will now update the table by allowing the constraints to it.
The table will have the foreign key now after alteration.
Remove the constraints from the table
For this purpose, we use ALTER command. This command will only remove the constraints from the table.
This is necessary to delete the whole table. But it is impossible to delete or drop such a table that has a join with the other table in the form of a foreign key. So firstly, we remove the fk_constraint of the firstly created table from the second one. Consider the table “designer” and child table “category”.
After seeing that the query is executed. Now apply the drop command.
Diagram represents the foreign keys within the tables. From the ERD representation, we took a snap to clarify the ambiguity regarding the constraints from the image; you can see the joins in the tables that we have created in this article.
“foreign key postgresql” shows the joining relation between two tables. Fetching the record of one table by causing a change in another can only be done through this constraint. The parent-child relation is built-in sharing the keys. This key can be introduced in creating or updating the table. Both methods are explained in this guide. These examples describe the importance of keys in all the tables used in any database.