Asked  7 Months ago    Answers:  5   Viewed   74 times

Can anybody help me to solve this problem?

There are 3 tables with 2 foreign keys:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Schema::create('firms', function (Blueprint $table) {
    $table->increments('id');
    $table->string('title')->nullable();
    $table->integer('user_id')->unsigned()->nullable();
    $table->foreign('user_id')->references('id')->on('users');
    $table->timestamps();
});

Schema::create('jobs', function (Blueprint $table) {
    $table->increments('id');
    $table->string('title')->nullable();
    $table->integer('firm_id')->unsigned()->nullable();
    $table->foreign('firm_id')->references('id')->on('firms');
    $table->timestamps();
});
                    

Error after running migration:

[IlluminateDatabaseQueryException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `job`.`#sql-5fc_a1`
   (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter ta
  ble `firms` add constraint `firms_user_id_foreign` foreign key (`user_id`)
  references `users` (`id`))

  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table `job`.`#sql-5fc_a1`
   (errno: 150 "Foreign key constraint is incorrectly formed")

 Answers

33

In case of foreign keys, the referenced and referencing fields must have exactly the same data type.

You create the id fields in both users and firms as signed integers. However, you create both foreign keys as unsigned integers, therefore the creation of the keys fail.

You need to either add the unsigned clause to the id field definitions, or remove the unsigned clause from the foreign key fields.

Wednesday, March 31, 2021
 
nighter
answered 7 Months ago
70

Answer to Question 1:
You can use advance where clause when using eloquent orm like in your case, use can try:

Foo::whereHas('fooTypes', function($query){
    $query->whereTypeId(5);  
})->get();  

Answer to Question 2:

Data Mapper or Query builder is faster than ORM Active Record. So when you are writing very large applications with significant number of concurrent requests then Data Mapper / Query Builder is the way to go.

On the other hand ORM provides a much cleaner syntax and better code readability for someone who just started with Laravel or is writing small or medium size app where the number of concurrent requests will not be significantly large.

Query Builder syntax is nearer to the plain SQL queries, which anyone who has worked with databases and sql finds easy to relate to.
So its a choice you have to make depending your comfort with the Query builder syntax as well as the size of app you are writing.

Laravel Eloquent vs Fluent query builder

Saturday, May 29, 2021
 
MannfromReno
answered 5 Months ago
20

Since you're referencing an id you need to make the foreign key column unsigned. As ids are by default unsigned (non-negative).

So do this for all your foreign keys:

$table->integer('product_id')->unsigned();
Saturday, May 29, 2021
 
IvanH
answered 5 Months ago
10

@JuanBonnett’s question has inspired me to find the answer. I used Laravel to automate the process without considering the creation time of the file itself. According to the workflow, “meals” will be created before the other table (categories) because I created its schema file (meals) before categories. That was my fault.

Thursday, June 17, 2021
 
RenegadeAndy
answered 5 Months ago
19

It's works the same way as FS_amount, just add the new columns to your code that generates the dynamic columns:

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(IF(month = ''',
      month,
      ''' and year(date) = ',
      year(date),
      ', FS_amount, NULL)) AS `',
      CONCAT('FA_',month),
      '_',
      year(date),
      '`, ', 
      'MAX(IF(month = ''',
      month,
      ''' and year(date) = ',
      year(date),
      ', AS_amount, NULL)) AS `',
      CONCAT('AS_',month),
      '_',
      year(date),
      '`'      
      )
    order by date
  ) INTO @sql
  FROM tmp_results;

You should have a look at the code the statements create (e.g. by temporary adding a select @sql;), although it should be pretty straight forward to add even more columns in case you'll need them.

Friday, August 20, 2021
 
Kirill Shlenskiy
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :