[Solved] Multiple foreign keys to the same table

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

[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.

[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" ?

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

[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.

[cleanzero]

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.

[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.

[cleanzero]

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.

[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.

[F3K Total]

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 (4.39 KiB) Viewed 81620 times

R

[cleanzero]

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.

[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.

[F3K Total]

A "Refresh Tables" wasn't enough?
R

[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!