Foreign key Postgresql

A foreign key is one or more columns having values that depend on the primary key. This constraint is used for joining two tables. In postgresql, a foreign key helps generate a relationship between the tables, making one table a parent table and the other a child. The foreign key can be created by using:

CREATE table query
UPDATE/ALTER query

Syntax

[CONSTRAINT name] FOREIGN KEY(columns)
REFERENCES parent_table(columns)
[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.

>> Create Table designer( designer_id INT GENERATED ALWAYS AS IDENTITY, designer_name VARCHAR(50) NOT NULL, PRIMARY KEY(designer_id));

In the table “designer”, designer_id is set as a primary key. Now create the second table.

>> create table category(category_id INT GENERATED ALWAYS AS IDENTITY, designer_id INT, category_id INT, category_name VARCHAR(200) NOT NULL, PRIMARY KEY(category_id), CONSTRAINT fk_designer FOREIGN KEY(designer_id) REFERENCES designer(designer_id))<strong>;</strong>

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.

CONSTRAINT fk_designer
FOREIGN KEY(designer_id)
REFERENCES designer(designer_id));

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.

NO ACTION

After the creation of tables, we use to enter values in it by “insert” command.

>> insert into designer(designer_name) VALUES (‘Ahmad shah’), (‘Sajjad hassan’);

Similarly in the case of second table, enter the values.

>> insert into category(designer_id, category_name) VALUES (1, ‘frock’), (1, ‘frock’), (2, ‘suit’),(2, ‘suit-1’);

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.

>> DELETE FROM designer WHERE designer_id = 1;

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.

>> CREATE TABLE employee ( emo_id GENERATED ALWAYS AS IDENTITY, emp_name VARCHAR(50) NOT NULL, PRIMARY KEY(emp_id) );

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.

>> create table info (info_id INT GENERATED ALWAYS AS IDENTITY, info_id INT, emp_id INT, info_name VARCHAR(200) NOT NULL, PRIMARY KEY(info_id), CONSTRAINT fk_employee FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE SET NULL);

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.

>> insert into employee (emp_name) Values (‘sophia smith’), ( ‘rubi williams’), (‘victoria gomex’);

>> insert into info (emp_id, info_name) VALUES ( 1, ‘clerk’), (1, ‘manager’), (2, ‘manager’), (3, ‘clerk’);

The delete command consists of the “where” clause to identify the id to be deleted from the row in the table employee.

>> DELETE FROM employee WHERE emp_id =2;

The relevant data will be deleted on the execution of the query. We will see the resultant table left after that query.

>> select * from info;

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.

>> create table nurse ( nurse_id int NOT NULL, nurse_name VARCHAR(55) NOT NULL, nurse_location VARCHAR (55) NOT NULL, PRIMARY KEY (nurse_ID) );

>> create table clinic clinic_id INT, nurse_id INT, clinic_details varchar(50) NOT NULL, clinic_type varchar(50) Not null);

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.

>> ALTER TABLE clinic ADD CONSTRAINT fk_nurse FOREIGN KEY (clinic_Id) REFERENCES nurse (nurse_ID) ON DELETE CASCADE ON UPDATE RESTRICT;

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”.

>> Alter table category DROP CONSTRAINT fk_designer;

After seeing that the query is executed. Now apply the drop command.

>> Drop table if exists designer;

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.

Conclusion

“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.