Asked  2 Years ago    Answers:  5   Viewed   713 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

4

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.

Thursday, November 3, 2022
 
3

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, November 5, 2022
 
2

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();
Friday, September 30, 2022
 
dreme
 
2

@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.

Monday, September 19, 2022
3

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.

Sunday, September 4, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages