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.

What is Foreign Key?

A field, or set of columns, in one table that uniquely identifies a row in another table, is called a Foreign key in PostgreSQL. Referring to the main key or a Unique key in the referred table creates a relationship between the two tables.

Referential integrity is guaranteed by this relationship, which necessitates that values in the foreign key column(s) either match values in the referenced key column(s) or be NULL.

Syntax:

CREATE TABLE table_name ( 
column1 data_type, column2 data_type , 
FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name) );

In PostgreSQL, it is feasible for a foreign key to have a null value. There are several ways that this could occur:

Methods to Have Nullable Foreign Key

In PostgreSQL, having a nullable foreign key column is possible, indicating that the row in the child table may not currently reference a specific row in the parent table, and the foreign key column may hold a null value. Here are methods to achieve this:

• Default Null
• Explicit Null Insert
• Updating to Null
• Deleting Referenced Row

1. Default Null

Inserting a row without providing a value for a column that is defined to have a default null value will result in a null value, which can meet the foreign key constraint if the foreign key column also permits nulls.

Example

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name varchar(255),
    value TEXT DEFAULT NULL
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    name varchar(255),
    table1_id INT REFERENCES table1(id)
);

--If you insert a row without giving table1_id a value, the result will be null

INSERT INTO table2 (id, name) VALUES (1, 'Aryan' );

SELECT *From table2;

Output:

Default NULL Method

Explanation: The SQL query retrieves all columns from table2. If a row was inserted into table2 without providing a value for table1_id, the result will include that row with table1_id as NULL.

2. Explicit Null Insert

If a column accepts Null values, you can directly insert a null value into it, even if it is a foreign key column.

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)
);

-- Inserting a row with a null value for table1_id
INSERT INTO table2 (id, name, table1_id) VALUES (1, 'Harsh' ,NULL);

SELECT *From table2;

Output:

Explicit NULL Method

Explanation: The provided SQL code creates two tables, table1 and table2. The INSERT statement adds a row to table2 with specific values, including a null value for table1_id. This demonstrates inserting a row into a table with a nullable foreign key column.

3. Updating to Null

You can update an existing record to set the foreign key column to null.

Example

--Parent Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

--Child Table

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    user_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE
);

INSERT INTO posts (title, content, user_id) VALUES ('Sample Post', 'Updating Post', NULL);

--To update an existing row in a posts

UPDATE posts SET user_id = NULL WHERE post_id = 1;

SELECT * From posts;

Output:

Updating Table Method

Explanation: The SQL code creates two tables, users and posts, with a foreign key relationship between them. The output shows a post with a NULL user_id after updating an existing row in the posts table, indicating the removal of the user association.

4. 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:

Delete Row Method

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.

Conclusion

In conclusion, PostgreSQL allows you to define a foreign key column that is capable of accepting NULL values, which gives your data model more flexibility. But it’s crucial to think about the effects of NULL values in foreign keys and make sure your application logic deals with them properly. While nullable foreign keys provide flexibility, managing NULL values is critical for maintaining data integrity and ensuring seamless application logic.