[Solved] Multiple foreign keys to the same table
[Solved] Multiple foreign keys to the same table
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 ????
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].
Reason: tagged [Solved].
OpenOffice 4.0.1 on MacOS 10.9.1
Re: Multiple foreign keys to the same table
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.
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
Re: Multiple foreign keys to the same table
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
Re: Multiple foreign keys to the same table
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)
*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
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Multiple foreign keys to the same table
cleanzero:
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.
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.An order could have a date and two foreign keys relative to receiver and sender. Receiver and sender belong to the Customers table
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Multiple foreign keys to the same table
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 wrote:cleanzero: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.An order could have a date and two foreign keys relative to receiver and sender. Receiver and sender belong to the Customers table
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.
OpenOffice 4.0.1 on MacOS 10.9.1
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Multiple foreign keys to the same table
As I understand it, some would assert that Access takes even greater liberties with Relational Theory than does SQL.In Access the multiple foreign key thing is doable
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Multiple foreign keys to the same table
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.Arineckaig wrote:As I understand it, some would assert that Access takes even greater liberties with Relational Theory than does SQL.In Access the multiple foreign key thing is doable
OpenOffice 4.0.1 on MacOS 10.9.1
Re: Multiple foreign keys to the same table
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.
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
Re: Multiple foreign keys to the same table
It is!MTP wrote:might be worth a try.
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 )
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Multiple foreign keys to the same table
Nice. So it works by using Sql statements. I just tried through the GUI.F3K Total wrote:It is!MTP wrote:might be worth a try.
This SQL Statementgives this result: RCode: 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 )
OpenOffice 4.0.1 on MacOS 10.9.1
Re: [Solved] Multiple foreign keys to the same table
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
Re: [Solved] Multiple foreign keys to the same table
A "Refresh Tables" wasn't enough?
R
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
Re: [Solved] Multiple foreign keys to the same table
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