Migration : unique(['field_1', 'field_2']) doesn't work

@jlrdw Those are belongsToMany relationships in a pivot table.

@vincent15000 If they are child records then are you sure you need unique?

@jlrdw Yes. User 1 can share his datas with User 2.

So there will be (1, 2) in the table. There is no need to add twice this couple of values.

But there can be (2, 1), (1, 3), ...

@vincent15000 wouldn't a related friends table be easier.

friends

2
127
414
1029
etc

Of course have the user_id column of the parent friend, i.e., parent friend there is 219. So all of them are friends to user with id of 219. Sorry if I mis-understand what you are after.

@jlrdw Your suggestion would work if I'm the only user of the app, but there are other users who will share their data with their own friends.

So I have to specify the owner_id in the table and not only the friend_id.

Or perhaps I don't have understood what you said me last.

@vincent15000 My example had 219 as parent. Okay user 400 would have their friends, user 1000 their friends, etc.

Look at it like accounts payable:

Company A
--- List of their payables

Company B
--- List of their payables

etc

@jlrdw I effectively have a simple pivot table with only 2 fields corresponding to both foreign keys.

But this doesn't solve my problem about the unique index based on both fields simultaneously.

@vincent15000 what you mean doesn't work ? you can input 1-1 two times ?

most time in my pivot table without any extra column, i will use

$table->primary(['owner_id', 'friend_id']);

i will use unique on another column,

btw foreign key is auto indexed by database

@newbie360 Where did you see this notation ? I thought that Laravel doesn't manage double primary keys. Does it work for you ?

@vincent15000 double primary keys ?

$table->unsignedBigInteger('owner_id');
$table->unsignedBigInteger('friend_id');

$table->primary(['owner_id', 'friend_id']);

// this make me confuse, refs on same table same id
$table->foreign('owner_id')->references('id')->on('users');
$table->foreign('friend_id')->references('id')->on('users');

@newbie360 Yes I had understood. Perhaps I'm just confused between unique() and primary().

And yes I have two rows referenced to the same table, that's normal because it's a binding between two users and this works fine ;).

@vincent15000 This is my preferred approach:

$table->foreignId('owner_id')->constrained('users')->onDelete('cascade');
$table->foreignId('friend_id')->constrained('users')->onDelete('cascade');

$table->primary(['owner_id', 'friend_id']);

It's a composite key.

@MichalOravec Thank you that's a cleaner way to write the same thing ;).

@vincent15000 a 2009 post

https://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql

@newbie360 Thank you, this is really not a problem for me, I know theses differences, my question was only on how to declare a double unique index ;) in the migration.