What to call a column that references another row in the same table?

Question

I have inherited a table that contains a column that is used to "chain" between various related rows within the same table.

For instance: id, bar, reference_id

• 1, 'foo', 2
• 2, 'foo', 3
• 3, 'foo'

I have no idea what to call this structure in order to search for how I should deal with it. The idea is that the table maintains a sort of living history within itself. (my problem is that I may have row 1 or 2, but I need to get to row 3 somehow that is agnostic to the number of 'levels' that I need to traverse)

Answer 1

There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.

What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.

They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!

Something like:

SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;

... will get you started on your data.

Answer 2

A term you'll see used for this type of table is hierarchical data/table/queries – the table represents a hierarchy, or parent/child relationship of some form. (Sometimes they're simply referred to as trees.)

Oracle has specific SQL extensions for hierarchical queries, namely the start with/connect by keywords.

If you're only looking for leaf rows (i.e. rows with no next "pointer"), you can use the CONNECT_BY_ISLEAF in a where clause. Something like:

select
  name
from
  foo
where
  CONNECT_BY_ISLEAF = 1
connect by
  prior nxt = id
start with
  id = 1;