References to columns in same table

Databases
1

I want a column to refer to the primary key of other entries in the same table. In essence like this:

CREATE TABLE entries
(
entry_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR NOT NULL,
entry_reference SMALLINT UNSIGNED, 
FOREIGN KEY (entry_reference) REFERENCES entries (entry_id)
) ENGINE=InnoDB;

What are the possibilities to achieve such functionality. It is a foreign key also, of course.

EDIT: Nevermind, it seems to have merely been a syntax error with the varchar declaration.

2

I’m not sure what the purpose of a self-reference would be in your generic example.

I can think of a different example…in users tables, I’ve seen a “created/added by” field that references the userID.

3

parent/child, category/subcategory, productmaster/product, ponzi scheme downlines, …

there are lots of examples

:slight_smile: