Could you point how to correctly add a FK to an existent table by using Laravel migrations?
Hi Experts
Could you point how to correctly add a FK to an existent table by using Laravel migrations?
The migration code I used:
With these errors:
Thanks in advance
Could you point how to correctly add a FK to an existent table by using Laravel migrations?
The migration code I used:
...
public function up()
{
Schema::table('padronizacaorevendas', function (Blueprint $table) {
//
$table->foreign('id')->references('id')->on('statusparticipantes')->nullable();
});
}With these errors:
Thanks in advance
ASKER
Hi Chris
Just one thing I'm considering here that differs from the other question is how to get the same functionality by using Laravel's migration code.
Hi Eduardo,
The problem isn't with your Migration code - that's the correct way of adding a foreign key. The problem is with the data you have in your database (specifically the default value of columns), which is why the problem is simliar to your other question.
Let's say we have 2 tables - An Orders table and a Customer table. Assume the Customer Table has this:
ID: 1 | Name: Chris
ID: 2 | Name: Eduardo
And assume the Order table has this:
ID: 1 | Amount: $100.00
ID:2 | Amount: $150.00
Currently they're not linked. If we want to link them, we go about adding a new field to the Order table called CustomerID. If we create that field as an INT NOT NULL, then the default value for that column will be 0 and the Order table will look like this:
ID: 1 | Amount: $100.00 | CustomerID: 0
ID:2 | Amount: $150.00 | CustomerID: 0
Now if we try and add a foreign key with either SQL:
ALTER TABLE Order ADD FOREIGN KEY (CustomerID) REFERENCES Customer(ID);
or with Laravel:
$table->foreign('CustomerI
Then it will fail. The reason it will fail is because for that Foreign Key constraint to work, the Customer table would need to have a record with an ID of 0 (which it does't) to match the value we have in our orders table.
The ay around this is to make sure that when you add the foreign key column (CustomerID in my example), you make sure that it's NULLABLE. This way, the default value will be NULL, so it won't try and match a related record.
In Laravel, you set a column type to null with the nullable() method, so use that in your migration for creating the original table:
$table->integer('CustomerI
The problem isn't with your Migration code - that's the correct way of adding a foreign key. The problem is with the data you have in your database (specifically the default value of columns), which is why the problem is simliar to your other question.
Let's say we have 2 tables - An Orders table and a Customer table. Assume the Customer Table has this:
ID: 1 | Name: Chris
ID: 2 | Name: Eduardo
And assume the Order table has this:
ID: 1 | Amount: $100.00
ID:2 | Amount: $150.00
Currently they're not linked. If we want to link them, we go about adding a new field to the Order table called CustomerID. If we create that field as an INT NOT NULL, then the default value for that column will be 0 and the Order table will look like this:
ID: 1 | Amount: $100.00 | CustomerID: 0
ID:2 | Amount: $150.00 | CustomerID: 0
Now if we try and add a foreign key with either SQL:
ALTER TABLE Order ADD FOREIGN KEY (CustomerID) REFERENCES Customer(ID);
or with Laravel:
$table->foreign('CustomerI
Then it will fail. The reason it will fail is because for that Foreign Key constraint to work, the Customer table would need to have a record with an ID of 0 (which it does't) to match the value we have in our orders table.
The ay around this is to make sure that when you add the foreign key column (CustomerID in my example), you make sure that it's NULLABLE. This way, the default value will be NULL, so it won't try and match a related record.
In Laravel, you set a column type to null with the nullable() method, so use that in your migration for creating the original table:
$table->integer('CustomerI
ASKER
Hi Chris
In my previous code I considered it nullable...
Your explanation is perfect, as we see at the previous question.
But after repeating the migration it failed again, maybe something wrong with the code.
In my previous code I considered it nullable...
$table->foreign('id')->references('id')->on('statusparticipantes')->nullable();Your explanation is perfect, as we see at the previous question.
But after repeating the migration it failed again, maybe something wrong with the code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the help.
Regardless of how you're adding the foreign key (SQL / Migration) the problem you're having here is the same as the one you're having on your other question. It's down to not having matching values in the related table. Please refer to my answer from your other question, as this is effectively a duplicate question, and should be closed.