I'm new to OpenOffice Base and have limited experience with relational databases. I'm trying to link two tables. I've created a primary key in the first table called ID that auto generates a unique number. How do I create a foreign key in the second table that references the ID field in the first table? I can't find anything in the documentation on this.
Thanks
Foreign Keys
Re: Foreign Keys
For HSQLDB and similar types of databases call Tools>SQL and execute ...
"TBL_B" is the many-side of the one-to-many-relation with foreign key "AID". TBL_A is the 1-side with primary key "ID".
The linked fields have to be put in braces because a foreign key can have more than one field on each side.
Finally call menu:View>Refresh Tables in order to propagate the modified database to the Base GUI.
The modification fails with an integrity error if the tables contain data that would violate the key, e.g. if "TBL_B"."AID" contains values without equivalent value in "TBL_A"."ID".
------- GUI Method ----------------------------------------------
menu:Tools>Relations...
Add the two tables side by side and draw a line between the 2 fields.
However, this graphical tool is a little bit awkward. Sometimes it fails to create a valid relation. Close and re-open that window in this case. Sometimes you have to restart the whole application.
Image: one-to-many (1-n) relation between "Prefs" and "Persons". many-to-many (m-n) relation between "Persons" and "Items" through mapping table "Persons_Items".
Code: Select all
ALTER TABLE "TBL_B" ADD FOREIGN KEY ("AID") REFERENCES "TBL_A"("ID");
The linked fields have to be put in braces because a foreign key can have more than one field on each side.
Finally call menu:View>Refresh Tables in order to propagate the modified database to the Base GUI.
The modification fails with an integrity error if the tables contain data that would violate the key, e.g. if "TBL_B"."AID" contains values without equivalent value in "TBL_A"."ID".
------- GUI Method ----------------------------------------------
menu:Tools>Relations...
Add the two tables side by side and draw a line between the 2 fields.
However, this graphical tool is a little bit awkward. Sometimes it fails to create a valid relation. Close and re-open that window in this case. Sometimes you have to restart the whole application.
Image: one-to-many (1-n) relation between "Prefs" and "Persons". many-to-many (m-n) relation between "Persons" and "Items" through mapping table "Persons_Items".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice