Directionless relationship failing in PostgreSQL

Question

I am trying to create a 2-way relationship table in PostgreSQL for my 3 objects. This idea has stemmed from the following question https://dba.stackexchange.com/questions/48568/how-to-relate-two-rows-in-the-same-table where I also want to store the relationship and its reverse between rows.

For context on my database: Object 1 which contains (aka relates to many) object2s. In turn, these object2s also relate to many object3s. A 1-to-many relationship (object 1 to object 2) and many-to-many relationship (object 2 to object 3)

Each of the objects have been assigned a UUID in other tables which contain info regarding them. Based on their UUID's I want to be able to query them and get the associated objects UUID as well. This in turn will show me the associations and direct me as to which object I should be looking at for location, info, etc just by knowing the UUID.

PLEASE NOTE - THAT ONE BOX MAY HAVE A RELTIONSHIP OF 10 SLOTS. THEREFORE THAT ONE UUID ASSIGNED FOR THE BOX WILL APPEAR IN MY UUID1 COLUMN 10 TIMES!! THIS IS A MUST!

My next step was to try and create a directionless relationship using this query:

CREATE TABLE bridge_x 
(uuid1 UUID NOT NULL REFERENCES temp (uuid1), uuid2 UUID NOT NULL REFERENCES  temp (uuid2), 
PRIMARY KEY(uuid1, uuid2), 
CONSTRAINT  temp_temp_directionless 
FOREIGN KEY (uuid2, uuid1) 
REFERENCES bridge_x (uuid1, uuid2)
);

Is there any other way I can store ALL the information mentioned and be able to query the UUID in order to see the relationship between the objects?

Answer 1

You'll need a composite primary key in the bridge table. An example, using polygameous marriages:

---

CREATE TABLE person
        (person_id INTEGER NOT NULL PRIMARY KEY
        , name varchar NOT NULL
        );

CREATE TABLE marriage
        ( person1 INTEGER NOT NULL
        , person2 INTEGER NOT NULL
        , comment varchar
        , CONSTRAINT marriage_1 FOREIGN KEY (person1) REFERENCES person(person_id)
        , CONSTRAINT marriage_2 FOREIGN KEY (person2) REFERENCES person(person_id)
        , CONSTRAINT order_in_court CHECK (person1 < person2)
        , CONSTRAINT polygamy_allowed UNIQUE (person1,person2)
        );

INSERT INTO person(person_id,name) values (1,'Bob'),(2,'Alice'),(3,'Charles');

INSERT INTO marriage(person1,person2, comment) VALUES(1,2, 'Crypto marriage!') ; -- Ok
INSERT INTO marriage(person1,person2, comment) VALUES(2,1, 'Not twice!' ) ; -- Should fail
INSERT INTO marriage(person1,person2, comment) VALUES(3,3, 'No you dont...' ) ; -- Should fail
INSERT INTO marriage(person1,person2, comment) VALUES(2,3, 'OMG she did it again.' ) ; -- Should fail (does not)
INSERT INTO marriage(person1,person2, comment) VALUES(3,4, 'Non existant persons are not allowed to marry !' ) ; -- Should fail

SELECT p1.name, p2.name, m.comment
FROM marriage m
JOIN person p1 ON m.person1 = p1.person_id
JOIN person p2 ON m.person2 = p2.person_id
        ;

---

Result:

---

CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 1
ERROR:  new row for relation "marriage" violates check constraint "order_in_court"
DETAIL:  Failing row contains (2, 1, Not twice!).
ERROR:  new row for relation "marriage" violates check constraint "order_in_court"
DETAIL:  Failing row contains (3, 3, No you dont...).
INSERT 0 1
ERROR:  insert or update on table "marriage" violates foreign key constraint "marriage_2"
DETAIL:  Key (person2)=(4) is not present in table "person".
 name  |  name   |        comment        
-------+---------+-----------------------
 Bob   | Alice   | Crypto marriage!
 Alice | Charles | OMG she did it again.
(2 rows)