Foreign Keys

Creating tables and queries
Post Reply
wdl0355
Posts: 1
Joined: Fri Jul 21, 2017 3:12 am

Foreign Keys

Post by wdl0355 »

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
OPEN OFFICE 4.1.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Foreign Keys

Post by Villeroy »

For HSQLDB and similar types of databases call Tools>SQL and execute ...

Code: Select all

ALTER TABLE "TBL_B" ADD FOREIGN KEY ("AID") REFERENCES "TBL_A"("ID");
"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
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
Post Reply