PostgreSQL Add Foreign Key If Not Exist

by
PostgreSQL Add Foreign Key If Not Exist Registrations to Courses

In this PostgreSQL tutorial, how to add a foreign key if not exist in PostgreSQL, where you will understand why you need to add the foreign key to tables.

Also, you will use the PL\PGSQL with table pg_constraint to check whether the existing table has a foreign key or not. I will also explain to you how to use the ALTER TABLE command with clause ADD CONSTRAINT and FOREIGN KEY to add the foreign key to the table.

PostgreSQL Add Foreign Key If Not Exist

The FOREIGN KEY is an important concept in relational databases because it ensures data integrity by creating relationships between tables. Sometimes a new table is introduced in the database, you may need to add the FOREIGN KEY dynamically to the table to establish relationships with other tables in the database.

The syntax to add a foreign key is given below.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES referenced_table(referenced_column);
  • ALTER TABLE: This is the command to alter the properties of the table.
  • table_name: The table name to which you want to add the foreign key.
  • ADD CONSTRAINT constraint_name: The name that you want to assign to a foreign key.
  • FOREIGN KEY column_name: The column that is going to be the foreign key in your table.
  • referenced_table: The table where the primary key exists.
  • refrenced_column: The name of the primary key column that exists in the refrenced_table.

But when you use the above syntax to add the foreign key, it doesn’t check for the table with an already existing foreign key. So for that here you will use a combination of PL/PGSQL and the system catalog called pg_constraint.

The syntax is given below.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'your_constraint_name') THEN
        ALTER TABLE table_name
        ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
        REFERENCES referenced_table(referenced_column);
    END IF;
END
$$;

In the above syntax within BEGIN, the IF NOT EXISTS checks if the constraint with the given name exists in the pg_constraint. If the given name constraint doesn’t exist, then the statement within the IF block is executed to add the foreign key to the table.

The pg_constraint is the system catalogue that stores all the constraints of the database in PostgreSQL.

Let’s check with different examples of how to add the foreign key to the existing table.

Suppose a database developer at the E-commerce platform and as the platform scales, there is a need for new table order_items in the database but the database contains two already existing tables of products and orders.

So the task of the database developer is to establish a relationship between order_items and the table’s products and orders. So that the order_items represent appears in the order and each order item is linked with products.

PostgreSQL Add Foreign Key If Not Exist Tables

To add the foreign key to the order_items use the below query.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_orderitems_productid') THEN
        ALTER TABLE order_items
        ADD CONSTRAINT fk_orderitems_productid FOREIGN KEY (product_id) REFERENCES products(product_id);
    END IF;
END
$$;
PostgreSQL Add Foreign Key If Not Exist

The above query, first, checks for the foreign key ‘fk_orderitems_productid’ with this name exists in the system catalogue pg_constraint and from the output, there is no foreign key with this name.

So the statement after the THEN keyword is executed and the foreign key named ‘fk_orderitems_productid‘ is added to the table order_items to a column product_id.

To check whether the added foreign key constraint is working or not execute the below command.

First, insert the data in the table products by executing the below command.

INSERT INTO products (name, price) VALUES ('Laptop', 10.0);

Also, insert the orders.

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);

Then insert the order items.

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
PostgreSQL Add Foreign Key If Not Exist Inserting Order items

Successfully inserted the order items with order ID and product ID equal to 1 and the quantity equal to 2.

Let’s insert the new order items with product ID equal to 10.

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 10, 2);
PostgreSQL Add Foreign Key If Not Exist Checking Constraints

As you can see in the output, when you execute the above insert statement throws an error which means the product with an ID equal to 10 doesn’t exist. This means the added foreign key constraint is working and the order_items table is lined with the products table.

Let’s take one more example, suppose you are a backend developer and you have a task given by the university to add the registration table that shows which students are registered in which course.

The University database has tables of students and courses and you added the table registration, now you have to add the foreign key to the tables. The tables are shown below.

Tables PostgreSQL Add Foreign Key If Not Exist

Now add the foreign key from registration to students using the below code.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_registrations_studentid') THEN
        ALTER TABLE registrations
        ADD CONSTRAINT fk_registrations_studentid FOREIGN KEY (student_id) REFERENCES students(student_id);
    END IF;
END
$$;
PostgreSQL Add Foreign Key If Not Exist Registrations to Students

The above query, first, checks for the foreign key ‘fk_registrations_studentid’ with this name exists in the system catalogue pg_constraint and from the output, there is no foreign key with this name.

So the statement after the THEN keyword is executed and the foreign key named ‘fk_registrations_studentid‘ is added to the table registrations to a column student_id.

Also, add the foreign key from registrations to students using the below code.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_registrations_courseid') THEN
        ALTER TABLE registrations
        ADD CONSTRAINT fk_registrations_courseid FOREIGN KEY (course_id) REFERENCES courses(course_id);
    END IF;
END
$$;
PostgreSQL Add Foreign Key If Not Exist Registrations to Courses

The above query, first, checks for the foreign key ‘fk_registrations_courseid’ with this name exists in the system catalogue pg_constraint and from the output, there is no foreign key with this name.

So the statement after the THEN keyword is executed and the foreign key named ‘fk_registrations_courseid’ is added to the table registrations to a column course_id.

You have successfully added the foreign key constraint from registrations to students and courses. This is how to add the foreign key to the tables.

Conclusion

In this PostgreSQL tutorial, you learned how to check foreign keys to the already existing table before adding a new one to the same table. Also, you learned how to use the PL\PGSQL with the system catalogue table pg_constraint to check the existence of foreign keys.

You may also read: