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?
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)