Level 63
I don't think that it's possible.
The only way I see is to handle this manually instead of letting MySQL or Laravel do this automatically.
Aug 22, 2023
11
Level 1
Laravel foreign key on two columns.
Hi, I have two tables: one is 'event_player_lists'
Schema::create('event_player_lists', function (Blueprint $table) {
$table->id();
$table->foreignId('event_id')->constrained('events')->cascadeOnDelete();
$table->unsignedInteger('player_imported_id')->index();
$table->string('role', 50);
$table->timestamps();
});
and another one is:
Schema::create('event_winner_players', function (Blueprint $table) {
$table->id();
$table->foreignId('event_id')->constrained('events')->cascadeOnDelete();
$table->unsignedInteger('player_imported_id');
$table->string('vote')->nullable();
$table->float('point');
$table->foreign('player_imported_id')->references('player_imported_id')->on('event_player_lists')->cascadeOnDelete();
$table->timestamps();
});
Now, My question is.
How can I create a foreign key relationship between these two tables based on event_id,player_imported_id.
Reason: Now as my all event player import id is the same, So when I delete the event_player_list of ID: 3 events then all the winner_player_lists are deleted. If I restrict this then also I can't delete any event player list.
Problem: When I delete event 3 player_list, due to the same import id they make the winner_player empty.
The solution I want: When I delete event 3 player_list, they should delete only the winner_players_list which has event_id 3.
Level 73
I don't know if you can do it with the nice syntax, but you can do it with plain SQL.
CREATE TABLE MyReferencingTable AS (
[COLUMN DEFINITIONS]
refcol1 INT NOT NULL,
rofcol2 INT NOT NULL,
CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
REFERENCES OtherTable(col1, col2)
) ENGINE=InnoDB;
Stolen from: https://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql
1 like
Level 1
@Tray2 sir, Is there any way to delete data without deleting event_player_lists without deleting event_winner_players? Now Laravel deletes them automatically.
Thanks for the reply, sir.
1 like
Level 73
@bmtamim Laravel doesn't delete them it's the database that does that, just add
ON DELETE CASCADE
To your foreign key.
1 like
Level 1
@Tray2 Yes, I know sir, But I don't want ON Delete Cascade. I want NO Action, But NO ACTION Restrict the delegation of the parent table row.
1 like
Level 73
@bmtamim You can tell it to do nothing.
https://stackoverflow.com/questions/12096790/why-to-use-foreign-keys-with-no-action-on-delete-or-update
1 like
Level 63
@Tray2 I know that it's not possible with Laravel, but I didn't know that it was possible with plain SQL.
Level 73
@vincent15000 The database can do a lot of things, but many just see it a data storage.
1 like
Level 63
@Tray2 That's not false, I'm part of these people that consider a database as a data storage, but it's an intelligent data storage.
When you say that a database can do a lot of things, what do you think about ?
For me a database can store data, update or delete on cascade for the relationships, have views, ... did you think about something else ? If yes, have you perhaps some web links to share ?
Level 73
@vincent15000 There are too many things to list all, but I'll list a few.
- There are tons of built in functions that you can use in your queries.
- Generated columns to help you manipulate the information in your table
- Views to store a complex query inside the database, so that the query you run is simpler.
- Federated tables where you fetch data from other places and store it as your own (materialized views in oracle)
- Stored custom function inside your database that you can then use in your queries.
- Triggers do things when data is added, changed or deleted
- Stored procedures that can be called to manipulate the data in the database
The list goes on.
You can read a bit about some of those things here
https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application
https://tray2.se/posts/using-table-triggers-to-log-changes-in-your-database-tables
1 like
Level 63
@Tray2 Ok thank you very much.
I already know a big part of the list. But not all.
Thank you for the links ;). I will read all this.
Please or to participate in this conversation.