New rule: warn when ON DELETE SET NULL targets a NOT NULL column #1136

Hi and thanks for this project!

I think I've found a rule that would be worth implementing:

Problem

A foreign key with ON DELETE SET NULL on a column declared NOT NULL is a silent logical error. The migration applies without complaint, but any deletion of the referenced row will fail at runtime with Postgres error 23502 (not_null_violation).

Squawk currently has no rule covering this case.

Example

CREATE TABLE conversations (
    id uuid PRIMARY KEY
);

CREATE TABLE messages (
    id           uuid PRIMARY KEY,
    conversation_id uuid NOT NULL,
    CONSTRAINT fk_messages_conversation_id
        FOREIGN KEY (conversation_id)
        REFERENCES conversations(id) ON DELETE SET NULL  -- contradicts NOT NULL above
);

Running this migration succeeds. But:

DELETE FROM conversations WHERE id = '...';
-- ERROR: 23502 null value in column "conversation_id" of relation "messages" violates not-null constraint

Suggested rule

ban-set-null-on-not-null-column (or similar): within the same SQL file, if a FOREIGN KEY ... ON DELETE SET NULL (or ON UPDATE SET NULL) references a column that is declared NOT NULL in a CREATE TABLE or ALTER TABLE ... ALTER COLUMN ... SET NOT NULL in the same file, emit a warning.

The fix is to either:

• Change the FK action to ON DELETE CASCADE (or RESTRICT/NO ACTION), or
• Drop the NOT NULL constraint from the column.