How can I represent a many-to-many relationship to a single table / entity in hibernation?

I'm not sure how you would do this without huge, horrible redundancy. The standard way to handle a many-to-many relationship between two tables is a third table containing two primary key values: one for the first table, one for the second table with a unique constraint (read "index") on the combination of all of these columns, and possibly with one or two duplicate (non-unique) indexes on separate primary keys. In the diagram:

CREATE TABLE Table1 (pkcol1 ... PRIMARY KEY, ...);
CREATE TABLE Table2 (pkcol2 ... PRIMARY KEY, ...);
CREATE TABLE MtoM_Table1_Table2
(
     pkcol1 ... REFERENCES Table1,
     pkcol2 ... REFERENCES Table2,
     PRIMARY KEY (pkcol1, pkcol2)
);
-- CREATE INDEX fk1_mtom_table1_table2 ON MtoM_Table1_Table2(pkcol1);
-- CREATE INDEX fk2_mtom_table1_table2 ON MtoM_Table1_Table2(pkcol2);

      

        
        
        
      

    

If your DBMS is smart, you can skip a separate index on the primary primary key column, as the primary key index can also be used when looking up the leading value only.

Suppose table 1 and table 2 are the same table (so, in fact, we only have table 1) as in the question; this usually requires the mapping table MtoM_Table1_Table1 - a separate table from the main table. The mapping table must have separate names for the PK (primary key) column, but both columns (or sets of columns) in the mapping table will refer to the PK columns in table 1.

CREATE TABLE Table1 (pkcol1 ... PRIMARY KEY, ...);
CREATE TABLE MtoM_Table1_Table1
(
     pkcol1 ... REFERENCES Table1(pkcol1),
     akcol1 ... REFERENCES Table1(pkcol1),
     PRIMARY KEY (pkcol1, akcol1)
);
-- CREATE INDEX fk1_mtom_table1_table1 ON MtoM_Table1_Table1(pkcol1);
-- CREATE INDEX fk2_mtom_table1_table1 ON MtoM_Table1_Table1(akcol1);

      

        
        
        
      

    

If you want to exclude the mapping table as well, you will have to have a second column in table1 to store another PK value - call it FKcol1 (for the foreign key column). Then that leaves you with a nuisance: what's the primary key? It must be a combination of PKCol1 and FKCol1. But FKCol1 must refer to the primary key of another row - so you have a contradiction. Even assuming that you managed to avoid it as a problem (how exactly?), To have "many rows" on the reference side of a many-to-many relationship, you must have multiple rows in the master table with the same data in all columns other than FKcol, but they will refer to a number (more than one, in total) of other rows in the table. It's a contradiction, plus a nightmare of redundancy, plus you've lost a simple primary key, plus it would be terrible to fixwhat's happening.

CREATE TABLE Table1
(
    pkcol1 ... /* PRIMARY KEY */,
    fkcol1 ... /* FOREIGN KEY REFERENCES Table1(pkcol1) */,
    ...
);
-- CREATE INDEX fk1_table1 ON Table1(pkcol1);
-- CREATE INDEX fk2_table1 ON Table1(fkcol1);

      

        
        
        
      

    

So, I am convinced that the only sensible answer is "No - you cannot represent both ends of a many-to-many relationship in one table, you must use a mapping table to keep a lot of chance of anything, as usual" in the system " ...