Split foreign keys in PostgreSQL

postgresqldrizzlemigrations
When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
1. First create the foreign key constraint without validation
2. Then validate existing data in a separate migration
Bad:
```sql
-- In a single migration

Install this rule for wispbit

Quick Install

Recommended
View install script

Run this one command to automatically install the rule:

curl -fsSL https://wispbit.com/api/install?rule=postgresql-split-foreign-key | bash

Manual install

1

Copy the rule

---
include: *.sql
---
When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
1. First create the foreign key constraint without validation
2. Then validate existing data in a separate migration
Bad:
```sql
-- In a single migration
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id);
```
Good:
```sql
-- In first migration: add without validating
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id)
NOT VALID;
-- In second migration: validate existing data
ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders;
```
2

Add the rule into your project

Save the copied content as: .wispbit/rules/postgresql-split-foreign-key.md

Install this rule for Coderabbit

Copy the configuration below and add it to your repository as .coderabbit.yml in your project root.

reviews:
  path_instructions:
    - path: "*.sql"
      instructions: |
                
        When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
        
        1. First create the foreign key constraint without validation
        2. Then validate existing data in a separate migration
        
        Bad:
        
        ```sql
        -- In a single migration
        ALTER TABLE users ADD CONSTRAINT fk_users_orders
        FOREIGN KEY (order_id) REFERENCES orders (id);
        ```
        
        Good:
        
        ```sql
        -- In first migration: add without validating
        ALTER TABLE users ADD CONSTRAINT fk_users_orders
        FOREIGN KEY (order_id) REFERENCES orders (id)
        NOT VALID;
        
        -- In second migration: validate existing data
        ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders;
        ```

Install this rule for Greptile

Greptile rules can be added through the web interface. Please see this documentation for details on how to add custom rules and context.

When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
1. First create the foreign key constraint without validation
2. Then validate existing data in a separate migration
Bad:
```sql
-- In a single migration
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id);
```
Good:
```sql
-- In first migration: add without validating
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id)
NOT VALID;
-- In second migration: validate existing data
ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders;
```

File Path Patterns:

*.sql

Install this rule for GitHub Copilot

Copilot instructions can be added through the interface. See the documentation for details on how to create coding guidelines.

When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
1. First create the foreign key constraint without validation
2. Then validate existing data in a separate migration
Bad:
```sql
-- In a single migration
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id);
```
Good:
```sql
-- In first migration: add without validating
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id)
NOT VALID;
-- In second migration: validate existing data
ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders;
```

File Path Patterns:

*.sql

Install this rule for Graphite Diamond

Diamond custom rules can be added through the interface. See the documentation for details on how to create custom rules.

When adding foreign keys in Postgres migrations, split the operation into two steps to avoid blocking writes on both tables:
1. First create the foreign key constraint without validation
2. Then validate existing data in a separate migration
Bad:
```sql
-- In a single migration
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id);
```
Good:
```sql
-- In first migration: add without validating
ALTER TABLE users ADD CONSTRAINT fk_users_orders
FOREIGN KEY (order_id) REFERENCES orders (id)
NOT VALID;
-- In second migration: validate existing data
ALTER TABLE users VALIDATE CONSTRAINT fk_users_orders;
```

File Path Patterns:

*.sql