PostgreSQL: Unexpected Foreign Key Constraint Violation on Insert with NULL Values
I'm reviewing some code and Quick question that's been bugging me - I'm working with a strange scenario when trying to insert rows into a PostgreSQL 13.3 table that has a foreign key constraint. The situation is that I have two tables: `orders` and `customers`. The `orders` table has a foreign key that references the `customers` table. For some reason, when I attempt to insert an order with a `customer_id` set to `NULL`, I receive a foreign key constraint violation behavior. Here's the relevant part of my schema: ```sql CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); ``` I assumed that inserting an order without a valid `customer_id` (i.e., `NULL`) would either succeed or be handled gracefully since I thought foreign key constraints only apply to non-null values. However, the behavior message I am getting is: ``` behavior: null value in column "customer_id" violates not-null constraint ``` I’ve tried checking the constraint definition, and it seems fine. I also verified that no additional constraints were added that would enforce the customer ID to be NOT NULL by default. This is an scenario because I need to allow orders to be created without a linked customer in certain scenarios. I attempted to bypass this by explicitly setting `customer_id` to a default value of `NULL` in my insert statement: ```sql INSERT INTO orders (customer_id) VALUES (NULL); ``` But this still results in the same behavior. I also checked for triggers on the table, but there are none defined. Any guidance on how to resolve this scenario or a better approach to design the table is appreciated! My development environment is Ubuntu. Any ideas what could be causing this?