What are Foreign Keys in PostgreSQL?
Foreign Keys in PostgreSQL are constraints used to enforce referential integrity between tables. It ensures that the data entered into a child table is valid and in accordance with the data in the parent table.
How To Create a Foreign Key in PostgreSQL
Let’s say you have a database of products and categories. The products table has columns for the product name, price, and quantity. The categories table has columns for the category name and description. You want to make sure that each product is assigned to a category. This is called maintaining the referential integrity of your data.
In a simplistic database system, this would be implemented by first looking up the category name in the categories table to see if it exists. If it does not exist, then the new product would not be inserted. This approach has a number of problems. First, it is inefficient. Second, it is error-prone. Third, it is not scalable.
PostgreSQL can do this for you using foreign keys. A foreign key is a constraint that is placed on a column or columns in a table. The foreign key constraint specifies that the values in the column or columns must match the values in a column or columns in another table.
To create a foreign key constraint in PostgreSQL, you use the FOREIGN KEY clause. The syntax is as follows:
FOREIGN KEY (column_name1, column_name2, ...)
REFERENCES table_name (column_name1, column_name2, ...)
[ON DELETE {CASCADE | RESTRICT | NO ACTION}]
[ON UPDATE {CASCADE | RESTRICT | NO ACTION}]
In this syntax, the FOREIGN KEY clause specifies the name of the column or columns in the current table that are the foreign key. The REFERENCES clause specifies the name of the table that the foreign key refers to and the name of the column or columns in the referenced table that the foreign key matches.
The ON DELETE and ON UPDATE clauses specify the action that should be taken when a row is deleted or updated in the referenced table. The possible values are:
CASCADE: Delete or update the row in the current table that references the deleted or updated row in the referenced table.
RESTRICT: Do not allow the row to be deleted or updated in the referenced table.
NO ACTION: Do nothing.
For example, to create a foreign key constraint that ensures that each product is assigned to a category, you would use the following SQL statement:
ALTER TABLE products ADD CONSTRAINT products_category_id_fkey FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
This statement would create a foreign key constraint named products_category_id_fkey on the category_id column in the products table. The constraint would ensure that the value in the category_id column must match the value in the id column in the categories table. If you try to delete or update a row in the categories table that has a product assigned to it, the operation will be prevented.
Benefits of Using Foreign Keys
Foreign Keys are vital in maintaining data consistency and accuracy. They also help to prevent data manipulation errors and ensure that no orphaned records exist.
Foreign keys provide a powerful mechanism for enforcing data integrity. You should always use