How deeply does PostgreSQL Mistakes and How to Avoid Them cover composite key?

Explains ordinary and NULL-containing keys through executable inserts, table output, index recreation, and corrected upsert behavior.

Composite key

As explained in PostgreSQL Mistakes and How to Avoid Them

A composite unique key is a uniqueness rule defined over multiple columns whose combined values identify a row combination.

A composite unique key makes the combined values of several columns identify a row combination. In an inventory table, (product_id, warehouse_id, area) distinguishes stock records and lets an upsert update a matching row instead of inserting another. section 2.11

The NULL upsert trap

A unique index on (product_id, warehouse_id, area) behaves as expected for area = 'freezer': a second upsert updates quantity from 10 to 20. With area = NULL, PostgreSQL treats the repeated composite values as distinct, so quantities 5 and 7 become separate rows instead of one row being updated. Avoid nullable columns in composite unique keys when possible. section 2.8

PostgreSQL 15 and later can change this behavior with NULLS NOT DISTINCT. Recreate the unique index with that clause so matching NULL values participate in conflict detection and the upsert updates the existing row. section 2.8

How PostgreSQL Mistakes and How to Avoid Them covers it

Cover of PostgreSQL Mistakes and How to Avoid Them

liveBook manning.com

Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

liveBook manning.com

100 SQL Server Mistakes and How to Avoid ThemA composite key is a key whose identity is formed from multiple columns.
liveBook manning.com

The Well-Grounded Data AnalystA composite primary key is a record key whose uniqueness is determined by a combination of multiple columns.
liveBook manning.com

Kafka Streams in Action, Second EditionA composite key is a record or row identifier formed from multiple fields.
liveBook manning.com

Java Persistence with Spring Data and Hibernate epubA key formed from multiple attributes or columns that jointly identify a persistent entity or relational row.
liveBook manning.com

The Art of CodeA composite key is a key formed from multiple values that together identify an item or entry.
liveBook manning.com

Building Web APIs with ASP.NET CoreA composite primary key is a primary key formed from multiple columns whose combined values uniquely identify a record.
liveBook manning.com

ScyllaDB in ActionA composite primary key is a primary key formed from multiple columns whose combined values identify a row.

Compare how 8 books explain composite key →

Related concepts

primary keykind of · A primary key is a designated column or attribute, or combination of them, that uniquely identifies each row or item in a table or data store.
dependent entityused by · A dependent entity is an entity whose identification or existence depends on a related owner entity and cannot be fully determined independently.
surrogate keyalternative to · A surrogate key is an artificially assigned, domain-independent identifier used to uniquely identify a database record independently of its business attributes.
keykind of · A key is an identifier or value used to locate or access an associated entry or property in a keyed data structure.
database tablepart of · A database table is a relational database structure that organizes records into rows and defined columns.
k-d treeused by · A k-d tree is a binary space-partitioning tree that organizes points in multidimensional space by recursively comparing coordinate dimensions.

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.