earmsby's avatar

Multiple foreign IDs constrained to same table

I'm getting myself confused about adding some fields to my LibraryItem table. I have table Author and a table LibraryItem. The Author table holds all the records for composers, editors, text authors and arrangers since there is cross-over (a person can be a composer and an editor and an arranger etc.)

In my LibraryItem table I need fields for composer_id, arranger_id, editor_id & text_author_id. They should all be constrained to the Author table but obviously I can't use "author_id" as the name of the field.

In the docs I see that I can do:

$table->foreignId('user_id')->constrained(
        table: 'users', indexName: 'posts_user_id'
    );

This seems to be what I'm looking for but I'm confused how to adapt this for my situation. Do I do:

$table->foreignId('composer_id')->constrained(
        table: 'authors', indexName: 'id'
    );

Or should I do it this way:

$table->foreignId('id')->constrained(
        table: 'authors', indexName: 'composer_id'
    );

Or neither and I should do it some other way?

1 like
4 replies
martinbean's avatar

@earmsby If you want several foreign keys pointing to different tables, then you need to give each column a distinct name, but then explicitly specify the table you’re referencing:

$table->foreignId('composer_id')->constrained('authors');
$table->foreignId('arranger_id')->constrained('authors');
$table->foreignId('editor_id')->constrained('authors');
$table->foreignId('text_author_id')->constrained('authors');

However, I don‘t really know what domain your application is in, but this might be better modelled as a many-to-many table between your “library items” and authors, where you can attach many authors to a library item but specify the role in your pivot table. This way, you can have as many attached authors, and roles, as you would like, without needing to change your database schema in the future (i.e. if for some reason you needed to support multiple arrangers for a library item).

2 likes
earmsby's avatar

@martinbean Thanks. I will give that a try.

I considered the pivot table solution but decided to go with 1 composer, 1 editor, etc. for this particular project. I have another project where I had the scenario you describe so I had an example of that.

1 like
LaryAI's avatar
LaryAI
AI
7 months ago
Level 58

You’re on the right track! You want multiple foreign keys in your library_items table, each referencing the id column on the authors table, but with different field names (composer_id, arranger_id, etc.).

Here’s how you do it:

$table->foreignId('composer_id')->constrained('authors');
$table->foreignId('arranger_id')->constrained('authors');
$table->foreignId('editor_id')->constrained('authors');
$table->foreignId('text_author_id')->constrained('authors');

Explanation:

  • By passing 'authors' to constrained(), you tell Laravel to reference the id column on the authors table.
  • The field names (composer_id, arranger_id, etc.) are up to you, and they will each get their own foreign key constraint.
  • You do not need to specify indexName unless you want to customize the index name (which is rarely necessary).

If you want the fields to be nullable (e.g., not every item has an arranger), just chain nullable():

$table->foreignId('arranger_id')->nullable()->constrained('authors');

Full Example:

Schema::create('library_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('composer_id')->constrained('authors');
    $table->foreignId('arranger_id')->nullable()->constrained('authors');
    $table->foreignId('editor_id')->nullable()->constrained('authors');
    $table->foreignId('text_author_id')->nullable()->constrained('authors');
    // other fields...
    $table->timestamps();
});

Summary:
Use foreignId('field_name')->constrained('authors') for each role. This is the standard way to set up multiple foreign keys to the same table in Laravel.

1 like
vincent15000's avatar

I think that you should do that with pivot tables, it would be easier to do and more evolutive.

You could for example have a pivot table with an additionnal field.

table : library_item_author

  • library_item_id
  • author_id
  • role

And the role would have one of those values : composer, arranger, editor, ...

If you have in the future another role, you have nothing to change in your database, just use the library_item_author with a new role value.

Please or to participate in this conversation.