Deleting Referenced Row
The foreign key field in the child table will be set to null if the relevant row in the parent table is deleted and the foreign key column does not have ON DELETE CASCADE defined.
Example:
CREATE TABLE table1 (
id SERIAL PRIMARY KEY,
name varchar(255)
);
CREATE TABLE table2 (
id SERIAL PRIMARY KEY,
name varchar(255),
table1_id INT REFERENCES table1(id)
);
INSERT INTO table2 (id, name , table1_id) VALUES (1, 'Ayush' ,NULL);
DELETE FROM table1 WHERE id = 1;
SELECT * From table2;
Output:
Explanation: The SQL code creates two tables, table1 and table2, with a foreign key relationship. After inserting a row into table2 with a NULL value for table1_id, deleting the referenced row in table1 results in a NULL value in table2’s foreign key column.
Foreign Key with a Null Value in PostgreSQL
PostgreSQL’s support for nullable foreign key columns provides flexibility in cases where child table rows may not reference specific parent table rows. However, managing these nullable foreign keys requires attention to maintain data integrity. Explore the methods and implications of implementing nullable foreign keys in PostgreSQL.
When using nullable foreign keys, there are a few trade-offs to take into account:
- Flexibility: It enables the representation of scenarios in which a child row may not require or have a valid parent at this time.
- Data Integrity: If nullable foreign keys are not managed appropriately, they may erode data integrity. They may result in “orphaned” rows in the child table, meaning that no data from the parent table is referenced in the child table.