mariadb

How to fix error on Foreign key constraint incorrectly formed in migrating a table in Laravel

by

When creating a new table in Laravel. A migration will be generated like: $table->bigIncrements(‘id’); Instead of (in older Laravel versions): $table->increments(‘id’); When using bigIncrements the foreign key expects a bigInteger instead of an integer. So your code will look like this: public function up() { Schema::create(‘meals’, function (Blueprint $table) { $table->increments(‘id’); $table->unsignedBigInteger(‘user_id’); //changed this line … Read more

How to turn on/off MySQL strict mode in localhost (xampp)?

by

->STRICT_TRANS_TABLES is responsible for setting MySQL strict mode. ->To check whether strict mode is enabled or not run the below sql: SHOW VARIABLES LIKE ‘sql_mode’; If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave +————–+——————————————+ |Variable_name |Value | +————–+——————————————+ |sql_mode |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION| +————–+——————————————+ Hence strict mode … Read more

Trouble with UTF-8 characters; what I see is not what I stored

by

This problem plagues the participants of this site, and many others. You have listed the five main cases of CHARACTER SET troubles. Best Practice Going forward, it is best to use CHARACTER SET utf8mb4 and COLLATION utf8mb4_unicode_520_ci. (There is a newer version of the Unicode collation in the pipeline.) utf8mb4 is a superset of utf8 … Read more

Cast int to varchar

by

You will need to cast or convert as a CHAR datatype, there is no varchar datatype that you can cast/convert data to: select CAST(id as CHAR(50)) as col1 from t9; select CONVERT(id, CHAR(50)) as colI1 from t9; See the following SQL — in action — over at SQL Fiddle: /*! Build Schema */ create table … Read more

Which is faster: multiple single INSERTs or one multiple-row INSERT?

by

https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions: Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) Inserting indexes: (1 × number of indexes) Closing: (1) From this it should be obvious, that sending one … Read more

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

by

Taken from Using FOREIGN KEY Constraints Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. It will reject any INSERT or UPDATE operation that attempts to create a foreign … Read more