Defaults¶
Schema¶
CREATE TABLE buildings (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT,
building_id INTEGER REFERENCES buildings
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department_id INTEGER NOT NULL REFERENCES departments
);
INSERT INTO buildings (id, name) VALUES
(1, 'London'),
(2, 'Paris');
INSERT INTO departments (id, name, building_id) VALUES
(1, 'Research', 1),
(2, 'Accounting', NULL);
INSERT INTO employees (id, name, department_id) VALUES
(1, 'John', 1),
(2, 'Jane', 1),
(3, 'Janet', 2);
all-outgoing-not-null¶
all-outgoing-not-null
is the minimum required relation.
Nullable outgoing foreign keys are ignored, as well as incoming foreign keys.
Fetching all departments
will make the building_id
foreign key null
.
Also, since no incoming relations are in the defaults, no rows in employees
are fetched.
If rows in buildings
are required, they can be enabled by adding an outgoing
relation from departments
to buildings
. This will also make the null
go away in the research department. See Outgoing relation.
Config
- relations:
- {defaults: all-outgoing-not-null}
- subject:
- tables:
- {table: departments}
Explain output
departments*
Results
INSERT INTO departments (id, name, building_id) VALUES(1, 'Research', NULL);
INSERT INTO departments (id, name, building_id) VALUES(2, 'Accounting', NULL);
all-outgoing-nullable¶
all-outgoing-nullable
ensures that all foreign keys are processed.
In this example it means that the buildings
row with id=1 must be fetched to satisfy the building_id
foreign key constraint on the departments
table.
Also, since no incoming relations are in the defaults, no rows in employees
are fetched.
Config
- relations:
- {defaults: all-outgoing-nullable}
- subject:
- tables:
- {table: departments}
Explain output
departments*
departments* -> departments.id=1 -> buildings.id=1
Results
INSERT INTO buildings (id, name) VALUES(1, 'London');
INSERT INTO departments (id, name, building_id) VALUES(1, 'Research', 1);
INSERT INTO departments (id, name, building_id) VALUES(2, 'Accounting', NULL);
all-incoming¶
all-incoming
ensures that for any row that is fetched all referencing foreign keys are processed in other tables.
In this example it means that that all employees
with department_id
in the fetched departments are fetched.
Note how no rows in buildings
are fetched, since all-outgoing-nullable
wasn’t enabled.
Config
- relations:
- {defaults: all-incoming}
- subject:
- tables:
- {table: departments}
Explain output
departments*
departments* -> departments.id=1 -> employees.department_id=1
departments* -> departments.id=2 -> employees.department_id=2
departments* -> departments.id=1 -> employees.department_id=1 -> employees.id=1 -> departments.id=1
departments* -> departments.id=2 -> employees.department_id=2 -> employees.id=3 -> departments.id=2
Results
INSERT INTO departments (id, name, building_id) VALUES(1, 'Research', NULL);
INSERT INTO departments (id, name, building_id) VALUES(2, 'Accounting', NULL);
INSERT INTO employees (id, name, department_id) VALUES(1, 'John', 1);
INSERT INTO employees (id, name, department_id) VALUES(2, 'Jane', 1);
INSERT INTO employees (id, name, department_id) VALUES(3, 'Janet', 2);
all-incoming and all-outgoing-nullable¶
The combination of all-outgoing-nullable
and all-incoming
, which is equivalent to everything, fetches everything in buildings
and employees
related to all the departments
.
Config
- relations:
- {defaults: all-outgoing-nullable}
- {defaults: all-incoming}
- subject:
- tables:
- {table: departments}
Explain output
departments*
departments* -> departments.id=1 -> buildings.id=1
departments* -> departments.id=1 -> employees.department_id=1
departments* -> departments.id=2 -> employees.department_id=2
departments* -> departments.id=1 -> buildings.id=1 -> departments.building_id=1
departments* -> departments.id=1 -> employees.department_id=1 -> employees.id=1 -> departments.id=1
departments* -> departments.id=2 -> employees.department_id=2 -> employees.id=3 -> departments.id=2
Results
INSERT INTO buildings (id, name) VALUES(1, 'London');
INSERT INTO departments (id, name, building_id) VALUES(1, 'Research', 1);
INSERT INTO departments (id, name, building_id) VALUES(2, 'Accounting', NULL);
INSERT INTO employees (id, name, department_id) VALUES(1, 'John', 1);
INSERT INTO employees (id, name, department_id) VALUES(2, 'Jane', 1);
INSERT INTO employees (id, name, department_id) VALUES(3, 'Janet', 2);
everything¶
This is equivalent to the above all-incoming and all-outgoing-nullable
Config
- relations:
- {defaults: everything}
- subject:
- tables:
- {table: departments}
Explain output
departments*
departments* -> departments.id=1 -> buildings.id=1
departments* -> departments.id=1 -> employees.department_id=1
departments* -> departments.id=2 -> employees.department_id=2
departments* -> departments.id=1 -> buildings.id=1 -> departments.building_id=1
departments* -> departments.id=1 -> employees.department_id=1 -> employees.id=1 -> departments.id=1
departments* -> departments.id=2 -> employees.department_id=2 -> employees.id=3 -> departments.id=2
Results
INSERT INTO buildings (id, name) VALUES(1, 'London');
INSERT INTO departments (id, name, building_id) VALUES(1, 'Research', 1);
INSERT INTO departments (id, name, building_id) VALUES(2, 'Accounting', NULL);
INSERT INTO employees (id, name, department_id) VALUES(1, 'John', 1);
INSERT INTO employees (id, name, department_id) VALUES(2, 'Jane', 1);
INSERT INTO employees (id, name, department_id) VALUES(3, 'Janet', 2);