One-to-many indexing

Question

I understand that postgres uses B tree /B+ Tree for indexing.I visualize each node in the tree as a key-value pair where the key represents the actual value being search for and the value is the location of that row in the disk.

This looks good for a one-to-one relationship.How does a BTree index for a one-to-many relation look like say a foreign key index - Each foreign key index can map to multiple rows in the child table.How does indexing happen here? A plain key-value pair based index doesnt seem to fit this case.

Answer 1

What you described in a strict sense only requires a single unique index on the referenced table, in order to constrain its values to uniquely identify the particular row that's being referenced. The index on the other table B that has references pointing to A is helpful, but not necessary.

You can add it and it'll help you for example find all rows in B that point at a particular A. That second index will not have to be of unique type, unless you specifically want to allow only 1:1 links. I think this might be what you needed cleared up:

• All unique constraints do require a unique index.
• To introduce a foreign key, you do need a unique constraint, and hence an index.
• Only the column that is being referenced requires the constraint. The column you reference it from, doesn't need it.
• Not all indexes have to be unique.