[Solved] Multiple foreign keys to the same table

Discuss the database features
Post Reply
cleanzero
Posts: 12
Joined: Thu Feb 20, 2014 5:42 pm

[Solved] Multiple foreign keys to the same table

Post by cleanzero »

I want to realize a simple database with shipping orders. Order can be sent and received by customers.

An order could have a date and two foreign keys relative to receiver and sender. Receiver and sender belong to the Customers table.

Something like this

Orders(ID,Date,id_sender,id_receiver)
A simplified customer table would be

Customers(ID,name, surname,city)
I noticed that I can't create two Customers/Orders relationships where one is relative to the foreign key id_sender and the other to id_receiver. I just tried with the OpenOffice DBMS and I get an error when trying to establish the second relationship because a relationship between Customers and Orders already exists and I get the error "ID in statement [ALTER TABLE "Orders" ADD FOREIGN KEY("id_sender","id_receiver") REFERENCES "Customers"("ID","ID")".
Is this a database design issue or something else ????
Last edited by Hagar Delest on Sun Feb 23, 2014 12:08 am, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.0.1 on MacOS 10.9.1
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Multiple foreign keys to the same table

Post by MTP »

I'm not sure if this is a limitation of Base or of the HSQL database engine. It can be worked around, with methods depending on what you want out of the foreign key relationship.

As far as creating indexing for query speed, that's done with the first foreign key so it's not an issue. For enforcing that all entries in the Orders table must match an existing entry in the Customer table, that can be done at the form level.

If you want protection against deleting entries in the Customer table if they exist in either column of the order table, that would be a little trickier. I think it could be done with triggers, which would require a split database and an update to HSQL 2.3.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
cleanzero
Posts: 12
Joined: Thu Feb 20, 2014 5:42 pm

Re: Multiple foreign keys to the same table

Post by cleanzero »

A bit of context. I'm teaching database theory at school. Recently we switched from Access to Base due to license issues. I was just doing a simple example during a lesson and suddenly realized that I could not implement multiple foreign keys pointing to the same table. What do you mean by saying that it can be "worked around with methods" ?
OpenOffice 4.0.1 on MacOS 10.9.1
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Multiple foreign keys to the same table

Post by MTP »

When you tell the database to enforce a foreign key relationship, it does a few things for you:
*The database creates an index on the foreign key column, thus speeding up queries involving that column
*The database will not allow INSERTS into the foreign key column unless a matching entry exists in the reference table/column
*The database will not allow deletion of any reference table rows that have matching entries in the foreign key column

It is convenient to get all that behavior just by telling the database a foreign key exists. But if that engine-level enforcement isn't available you can still get all three things in other ways.

(Again, I'm not sure if this limitation is due to Base or to HSQLDB - if it's a limitation just of the HSQLDB version 1.8 that comes bundled with openoffice, then you could use Base as the frontend for either a newer version of HSQLDB or a different database altogether - more information at [Tutorial] Splitting an "embedded HSQL database" and Using Base as a frontend for MySQL)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Multiple foreign keys to the same table

Post by Arineckaig »

cleanzero:
An order could have a date and two foreign keys relative to receiver and sender. Receiver and sender belong to the Customers table
Lacking expertise in database theory, I hesitate to suggest there could well be three quite distinct entities comprised in your single Customers table: Receivers, Senders and Customers. Is there not scope for at least one but preferably a couple of (linking) tables - Receivers and/or Senders - between the Orders and Customers tables? Hence the issue might well be one of Relational "database design rather than something else." The extra table(s), however, are not needed for JOINs between the Order and Customer tables because the SQL alias can see the latter as two distinct tables. As C J Date indicates SQL does not always fully match relational theory.

Later Edit:
An alternate simpler, but questionable, method might be to add a second candidate key field to the Customer table to which the second foreign key in the Order table could be referenced. It would still be necessary for the two unique key fields in the Customer table to be kept in sync.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
cleanzero
Posts: 12
Joined: Thu Feb 20, 2014 5:42 pm

Re: Multiple foreign keys to the same table

Post by cleanzero »

Arineckaig wrote:cleanzero:
An order could have a date and two foreign keys relative to receiver and sender. Receiver and sender belong to the Customers table
Lacking expertise in database theory, I hesitate to suggest there could well be three quite distinct entities comprised in your single Customers table: Receivers, Senders and Customers. Is there not scope for at least one but preferably a couple of (linking) tables - Receivers and/or Senders - between the Orders and Customers tables? Hence the issue might well be one of Relational "database design rather than something else." The extra table(s), however, are not needed for JOINs between the Order and Customer tables because the SQL alias can see the latter as two distinct tables. As C J Date indicates SQL does not always fully match relational theory.

Later Edit:
An alternate simpler, but questionable, method might be to add a second candidate key field to the Customer table to which the second foreign key in the Order table could be referenced. It would still be necessary for the two unique key fields in the Customer table to be kept in sync.
In used one table because the idea was that a customer could act as a receiver or a as a sender. The database trick to solve the problem could be to use two new tables receiver abd sender in a 1:1 relationship with the customer table, even if this looks redundant to me. In Access the multiple foreign key thing is doable because when building the relationship it will automatically produce an aliased customer1 table.
OpenOffice 4.0.1 on MacOS 10.9.1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Multiple foreign keys to the same table

Post by Arineckaig »

In Access the multiple foreign key thing is doable
As I understand it, some would assert that Access takes even greater liberties with Relational Theory than does SQL.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
cleanzero
Posts: 12
Joined: Thu Feb 20, 2014 5:42 pm

Re: Multiple foreign keys to the same table

Post by cleanzero »

Arineckaig wrote:
In Access the multiple foreign key thing is doable
As I understand it, some would assert that Access takes even greater liberties with Relational Theory than does SQL.
As far as I know I don't see anything in relational theory against an entity/table that has multiple relationships with an other entity/table.
OpenOffice 4.0.1 on MacOS 10.9.1
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Multiple foreign keys to the same table

Post by MTP »

This lack in Base was identified as a bug back in 2005 - and it's now marked as fixed, closed in version 2.4 back in 2008? The comments say there's still a problem with not being able to create aliased tables (the query design view supports creation of aliased tables, it doesn't seem like it would be that big a deal to put the same functionality in the relationship gui), but say that it is possible to create a relationship between one field in one table and multiple fields in another.

Whatever the bug report says, I don't know how to get it to work. Maybe version 2.4 it did work, and it's been rebroken in newer versions? I suppose someone could see if they could download version 2.4 and check it out.

I also found a thread that says the second foreign key can be created with sql even though the gui doesn't work. The sql in that thread is a little different than what the OP was getting the error message on, might be worth a try.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
F3K Total
Volunteer
Posts: 1009
Joined: Fri Dec 16, 2011 8:20 pm

Re: Multiple foreign keys to the same table

Post by F3K Total »

MTP wrote:might be worth a try.
It is!
This SQL Statement

Code: Select all

CREATE TABLE "Customers"(ID INTEGER IDENTITY,"name" varchar(50), "surname" varchar(50),"city" varchar(50));
CREATE TABLE "Orders"(ID INTEGER IDENTITY,"Date" DATE,"id_sender" INTEGER,"id_receiver" INTEGER);
ALTER TABLE "Orders"  ADD CONSTRAINT FK_S FOREIGN KEY ("id_sender") REFERENCES "Customers"( ID ) ;
ALTER TABLE "Orders"  ADD CONSTRAINT FK_R FOREIGN KEY ("id_receiver") REFERENCES "Customers"( ID )
gives this result:
2FKs.png
2FKs.png (4.39 KiB) Viewed 80070 times
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
cleanzero
Posts: 12
Joined: Thu Feb 20, 2014 5:42 pm

Re: Multiple foreign keys to the same table

Post by cleanzero »

F3K Total wrote:
MTP wrote:might be worth a try.
It is!
This SQL Statement

Code: Select all

CREATE TABLE "Customers"(ID INTEGER IDENTITY,"name" varchar(50), "surname" varchar(50),"city" varchar(50));
CREATE TABLE "Orders"(ID INTEGER IDENTITY,"Date" DATE,"id_sender" INTEGER,"id_receiver" INTEGER);
ALTER TABLE "Orders"  ADD CONSTRAINT FK_S FOREIGN KEY ("id_sender") REFERENCES "Customers"( ID ) ;
ALTER TABLE "Orders"  ADD CONSTRAINT FK_R FOREIGN KEY ("id_receiver") REFERENCES "Customers"( ID )
gives this result:
2FKs.png
R
Nice. So it works by using Sql statements. I just tried through the GUI.
OpenOffice 4.0.1 on MacOS 10.9.1
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Solved] Multiple foreign keys to the same table

Post by MTP »

Just a quick note - when I tried this on an existing database, I had to close and reopen the .odb file before the GUI would show the new relationship.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
F3K Total
Volunteer
Posts: 1009
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Multiple foreign keys to the same table

Post by F3K Total »

A "Refresh Tables" wasn't enough?
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: [Solved] Multiple foreign keys to the same table

Post by MTP »

I bet it would have been enough. I just hadn't ever needed to refresh tables before and forgot that menu option was there!
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Post Reply