Hi Community - brand new LO Base user here!
I want to create a self-referencing table, where it can express a hierarchy by having a “Parent” column that is a FK reference to the table’s PK column. I can’t seem to do this using the “Relationship” window though, it does not seem possible to specify the same table for both sides of the relationship. Can I do this using Base’s native interface or would I need to run the FK creation via DDL?
TIA,
Steve
Why shouldn’t it work by Tools → Relationship? I have just tested: Table with ID (INTEGER, primary key) and FKID (INTEGER). Could connect them without any problem.
Tutorial and example database with a 1-to-many relation and a many-to-many relation: [Example] Relations reflected by list boxes in forms
No problems to do that with a Firebird embedded. Just do the table, and in Tools->Relationships you can drag the foreign key over the primary key and the relationship is established.
After that you can do, for instance, a form with a subform in the usual way just using the table in the form and the same table in the subform.
SelfRelation.odb (11,4 KB)
With SQL you can do anything that is missing in Base but allowed in the underlying database. The embedded HSQLDB is documented here: Chapter 9. SQL Syntax
A self-referencing foreign key in a HSQLDB:
alter table "TblHorses" add foreign key ("SireID") references "TblHorses" ("ID");Thanks Robert. I have added my table to the “Relations” window, and selected New Relation. The pop-up window allows me to select the table and it’s PK column in the first drop-downs but requires me to select a second table and my table does not appear in that drop down list.
I was unaware of the drag n drop technique jucsaca suggested - that worked fine!
Thanks jucasaca - I wasn’t aware of the drag n drop technique, that worked great! If it were a composite PK would it still work?
Thanks villeroy - I was able to use the visual drag n drop technique suggested by jucasaca. Good to know I can still turn to DDL if I need to though.