Handling Composite Primary Keys and Junction Tables

[ghislaineguerin]

Problem

For our most basic use cases, single-column foreign keys are enough to solve basic table linking problems. However, when creating many-to-many relationships, we generate a junction table. Based on research, in some cases, the primary key of a junction table is a composite of both linked tables' foreign keys.

Auto-generated IDs for all tables

For tables created within Mathesar, the system will automatically add a read-only column, assign it as a primary key and generate new unique values every time a record is added. However, in the case of junction tables, should we still generate an additional ID or create a composite PK from both referenced columns?

We'd have something like the table below. Where new records aren't saved unless both columns have values added.

Referencing a composite primary-key

If a user then wants to link this table to another, using the 'Link Table' functionality, would we add a composite foreign key, meaning we insert two columns rather than one?
Nothing is preventing users from selecting a table with a composite primary key for linking, so I think we need to address this.

Questions

• Do we add an auto-generated ID column to mapping (junction) tables?
• How do we handle tables with composite PK when using the 'Link Table' flow? Do we add two columns?

[seancolsen]

Usually, the primary key of a junction table is a composite of both linked tables' foreign keys

Interesting. In my experience I'm used to seeing (and designing) mapping tables with their own id column.

I'd suggest we move forward with the assumption that all primary keys will be single column only. That would imply:

• When we create a mapping table for a many-to-many relationship, we give it an id column too. So your mapping table above would have three columns instead of two.
• If someone wants to use Mathesar with an existing database that has composite primary keys, then that behavior would not (yet) be supported.

We could add support for composite primary keys later if necessary.

[ghislaineguerin]

@seancolsen I did some research and in most cases, a composite PK was being created. I have seen arguments for both. It will be much easier if we have a single-column PK.

By not supporting it, do you mean we'd remove those tables from the 'Link Table' selector?

[seancolsen]

By not supporting it, do you mean we'd remove those tables from the 'Link Table' selector?

That's a good question.

I think ideally the user would be able to choose any table. Then based on the table they choose we might display an error message. For example:

You cannot link to table "foobar" because it does not have a primary key.

Or

You cannot link to table "foobar" because its primary key is a composite key which Mathesar does not yet support.

[mathemancer]

I think the most featureful way without adding too much implementation work would be to attempt a natural join on the tables, see if it works, and show the user. Because joining would be in the context of creating a view, the harm if the results aren't what they were thinking aren't too bad. In the case of single-column keys, we'd be using the natural join anyway.

[pavish]

@kgodey I know we have the issues for supporting multi-column primary-key but I'm not sure how well/(to what depth) we intend to provide support for this right away.

@ghislaineguerin

Do we add an auto-generated ID column to mapping (junction) tables?

I believe we will create an auto-generated single column primary key for all tables we create, which includes mapping tables.

How do we handle tables with composite PK when using the 'Link Table' flow? Do we add two columns?

When connecting to an existing db which contains a table with multi-column primary-key and the user intends to link it to another table, we would essentially have to add all the columns that are part of the primary-key to the mapping table. @kgodey Should we handle this for the initial release?

[ghislaineguerin]

@pavish I think adding all the columns that are part of the primary key makes sense, in that case, does this conflict with the idea we had of renaming the columns that will be created? I assume we'd allow users to change both.

[pavish]

@ghislaineguerin I don't think it will conflict with renaming the columns, it should work similar to the single column approach, except we'll be having multiple columns. We can allow users to rename all of them.

There might be better ways to create FK references in multi-column PK cases. I think we can figure it out when the entire team replies.

[kgodey]

I don't think we should support creating multi-columns foreign keys in the alpha release. The only thing we should support is showing ones that already exist correctly when the user is viewing the table or the constraints modal.

[kgodey]

For our most basic use cases, single-column foreign keys are enough to solve basic table linking problems. However, when creating many-to-many relationships, we generate a junction table. Based on research, in some cases, the primary key of a junction table is a composite of both linked tables' foreign keys.

@ghislaineguerin We won't be implementing it this way. I explained how we would implement it in #443 but I'll reproduce the relevant tables here:

If we start with "Movie" and "Actor"

Movie

ID	Title	some other fields
1	Top Gun	...
2	True Romance	...
3	The Ghost and the Darkness	...
4	Wall Street	...
5	Interview with the Vampire	...

Actor

ID	Name	some other fields
6	Christian Slater	...
7	Val Kilmer	...
8	Tom Cruise	...
9	Michael Douglas	...

The mapping table we create will have the following structure:

Movie Actor Link

ID	Movie ID	Actor ID
1	1	7
2	1	8
3	2	6
4	2	7
5	3	7
6	3	9
7	4	9
8	5	6
9	5	8

Here:

• ID is the primary key of the Movie Actor Link table
• Movie ID is a column with a foreign key constraint pointing to the ID column of the Movie table
• Actor ID is a column with a foreign key constraint pointing to the ID column of the Actor table

Do we add an auto-generated ID column to mapping (junction) tables?

Yes.

How do we handle tables with composite PK when using the 'Link Table' flow? Do we add two columns?

We don't (at least, not for the initial release). We should show an error message as @seancolsen suggested here.

[mathemancer]

Do we add an auto-generated ID column to mapping (junction) tables?

While it is reasonable in many cases to use the two foreign keys in a junction table as a composite primary key, I think it would make the schema design less extendable for the user. In case a junction table grows into an entity in its own right, we'll want that primary key id column so that the table can be used in a similar manner to other tables.

We should almost certainly automatically add UNIQUE and NOT NULL constraints to the foreign key columns as a pair, however (this is basically the same constraint set that defines a primary key). If we don't have those constraints, using the table as a junction table is likely to have unexpected results. Aside: the NOT NULL would technically be enforced by the fact that each foreign key column is a primary key column in another table. I still think it would be wise to add the constraint to the junction table to avoid any confusion.

One advantage I can think of for making the pair a composite primary key would be that it makes it more obvious that the UNIQUE and NOT NULL constraints shouldn't be removed. However, I don't think that advantage outweighs the downsides listed in my first paragraph and others' comments.