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)
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.
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.
parent/child, category/subcategory, productmaster/product, ponzi scheme downlines, ...
there are lots of examples