A foreign key connects columns in one table to identifying rows in another. In PostgreSQL, it provides a safer replacement for many table-inheritance relationships, especially when declarative partitioning is required. section 4.1 section 4.5
Use foreign keys with two operational cautions:
- Do not assume PostgreSQL creates an index on the referencing column. Add one when queries filter or join through that column. section 6.10
- Avoid modifying PostgreSQL catalog metadata to bypass validation unless the risks are fully understood; catalog mistakes can corrupt or disable the database. section 4.1
Replacing inherited meetings
To replace an inherited meetings table, create a structurally similar table, add an event_id column, copy the data, and attach a foreign key: CREATE TABLE new_meetings (LIKE meetings); ALTER TABLE new_meetings ADD event_id int; ALTER TABLE new_meetings ADD CONSTRAINT event_id_fk FOREIGN KEY (event_id) REFERENCES events (id) NOT VALID;. The data can then be migrated, although live systems may also need triggers to replicate concurrent changes. section 4.1
QUERY PLAN
----------------------------------------------------------------------------
Aggregate (cost=314.84..314.85 rows=1 width=8) (actual time=0.957..0.957
rows=1 loops=1)
-> Nested Loop (cost=0.29..314.79 rows=18 width=0) (actual
time=0.121..0.954 rows=18 loops=1)
-> Seq Scan on customers c (cost=0.00..306.00 rows=1 width=8)
(actual time=0.107..0.938 rows=1 loops=1)
Filter: ((first_name = 'Aiden'::text) AND (last_name =
'Kowalski'::text))
Rows Removed by Filter: 13999
-> Index Only Scan using invoices_customer_idx on invoices
(cost=0.29..8.61 rows=18 width=8) (actual time=0.012..0.014 rows=18
loops=1)
Index Cond: (customer = c.id)
Heap Fetches: 0
Planning Time: 1.511 ms
Execution Time: 1.338 ms
(10 rows)
Compare the indexed execution plan: adding an index to invoices.customer changes the access path to an Index Only Scan and reduces execution time from 28.060 ms to 1.338 ms. section 6.10
