I need to create a duplicate Table in the Relationships window of LibreBase

Sums · May 14, 2019, 12:58pm

I have a Table in Base named Airports.
Its PK field is named Airport_ICAO_Code

I have another Table named Routes.
It is a Many to Many Table and has two Fields.
They are DepartureAirportICAO and DestinationAirportICAO.

I need to use the Airport_ICAO_Code field in the Airports Table as a FK for both Fields in the Routes Table.

I can do this in Sql Server by creating a Duplicate Airports Table in the Relationships Window but Im not allowed to create a duplicate in Base.

How can I solve this.

Thank you

Ratslinger · May 14, 2019, 3:52pm

Hello,

With many-to-many use an intermediate table. See this post → Should I use a separate table?

Sums · May 14, 2019, 5:06pm

The Routes table is an Intermediate table. The problem is both FKs have to come from the same Airport_ICAO_Code fileld in the Airports table

Ratslinger · May 14, 2019, 5:21pm

Did you look at the link? Intermediate table should have its own Primary key - don’t see that in your table. Am I missing something?

Sums · May 14, 2019, 5:27pm

I looked at the link. My Routes table should have two FKs. The combination of these is Unique and would be a Composite PK.

Ratslinger · May 14, 2019, 5:34pm

Sorry, up most of the night and not thinking quite clear. Have you tried establishing the FK using SQL? Relationship dialog not always the best.

Sums · May 14, 2019, 5:37pm

I had posted this just as you posted. Can I show you in a screenshot, Ive added a RouteID PK but I still have the same problem with the Fks. I haven’t tried sql yet. Don’t know how in Libre.

Ratslinger · May 14, 2019, 5:49pm

You can add a screen - edit question and use toolbar. First did you see my comment on SQL?

Just saw last addition - need a minute to look up. Foreign key, see this post → "Trips and Accommodation” need advice on table structure and relations in DB.

Sums · May 15, 2019, 11:47am

Not sure what you mean by adding a screen. I solved the problem by creating a DepartureAirport Table and a DestinationAirport Table. It will mean that I will have to update both tables instead of just updating an Airports Table. Trying to create a duplicate table just lead to a lot of messy sql code. Thanks for your help with this and other questions.

Ratslinger · May 15, 2019, 3:42pm

@Sums You are the one who mentioned showing a screenshot. Just noted on how to do what you asked.

Have no idea why you would duplicate the table. Have never needed to do that. Link displayed an example.