Not Null Columns

Schema

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER REFERENCES departments
);

ALTER TABLE departments ADD COLUMN primary_employee_id INTEGER REFERENCES employees;

INSERT INTO departments (id, name) VALUES
    (1, 'Managers'),
    (2, 'Engineers');

INSERT INTO employees (id, name, department_id) VALUES
    (1, 'John', 1),
    (2, 'Jane', 2),
    (3, 'Janet', NULL);

UPDATE departments SET primary_employee_id=1 WHERE id=1;

Not Null Columns

In this example, two tables, departments and employees both reference each other with nullable foreign keys. It makes no difference to the SQL generation which table comes first when generating the INSERT statements, so they are processed in alphabetical order with the departments table getting insert statements generated first. The value for primary_employee_id can’t be added until the rows have been inserted in to the employees table so an UPDATE statement for departments is needed after the employees rows have been inserted.

Config

- subject:
  - tables:
    - {table: departments}
  - relations:
    - {column: department_id, table: employees}

Explain output

departments*
departments* -> departments.id=1 -> employees.id=1
departments* -> departments.id=1 -> employees.department_id=1
departments* -> departments.id=2 -> employees.department_id=2
departments* -> departments.id=1 -> employees.id=1 -> departments.id=1
departments* -> departments.id=2 -> employees.department_id=2 -> employees.id=2 -> departments.id=2

Results

INSERT INTO departments (id, name, primary_employee_id) VALUES(1, 'Managers', NULL);
INSERT INTO departments (id, name, primary_employee_id) VALUES(2, 'Engineers', NULL);
INSERT INTO employees (id, name, department_id) VALUES(1, 'John', 1);
INSERT INTO employees (id, name, department_id) VALUES(2, 'Jane', 2);
UPDATE departments SET primary_employee_id=1 WHERE id=1;

Not Null Columns Switched

Let’s pretend there is some reason why a row can’t be inserted into departments without primary_employee_id being set due to a CHECK constraint. The SQL generation engine can be given a hint by using a not-null-columns rule. This results in the tables being processed in reverse order with employees getting inserts first, then departments. This results in the rows in the departments table having primary_employee_id set in the INSERT statement. The consequence of this is that the department_id on employees must be set late with an UPDATE statement.

Config

- not-null-columns:
  - {column: primary_employee_id, table: departments}
- subject:
  - tables:
    - {table: departments}
  - relations:
    - {column: department_id, table: employees}

Explain output

departments*
departments* -> departments.id=1 -> employees.id=1
departments* -> departments.id=1 -> employees.department_id=1
departments* -> departments.id=2 -> employees.department_id=2
departments* -> departments.id=1 -> employees.id=1 -> departments.id=1
departments* -> departments.id=2 -> employees.department_id=2 -> employees.id=2 -> departments.id=2

Results

INSERT INTO employees (id, name, department_id) VALUES(1, 'John', NULL);
INSERT INTO employees (id, name, department_id) VALUES(2, 'Jane', NULL);
INSERT INTO departments (id, name, primary_employee_id) VALUES(1, 'Managers', 1);
INSERT INTO departments (id, name, primary_employee_id) VALUES(2, 'Engineers', NULL);
UPDATE employees SET department_id=1 WHERE id=1;
UPDATE employees SET department_id=2 WHERE id=2;