How to fix (errno: 150 “Foreign key constraint is incorrectly formed”) MySQL, MariaDB

If you catch errno: 150 “Foreign key constraint is incorrectly formed” when you try to create a new table in mySQL(or another relational DB) then you can use a simple way to find a bad foreign key(s). Just execute this query:

SELECT
  CONSTRAINT_NAME, 
  TABLE_NAME, 
  COLUMN_NAME, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = '<your table name here>' OR TABLE_NAME = '<your table name here>';

After that you can see where this bad table using and you can go and delete bad foreign key(s).

Happy SQLing)

UPDATE with random number between range 1-n [MYSQL UPDATE RAND]

Sometimes you need to update your latest added field in DB table by random numbers.
For that reason you can use this simple SQL command:
[1 – min, 100-max]

UPDATE `...` 
SET `columnName` = FLOOR( 1 + RAND( ) *100 );
WHERE ...

In this way you can easy update `columnName` by random value in defined range.
Happy SQLing… Continue reading UPDATE with random number between range 1-n [MYSQL UPDATE RAND]

How to add one/two/n days or hours to date in MySQL (DATE_ADD in MYSQL)

Suppose you have a table in the database where the stored data and you need to check or get that dates but with the changes – calculate directly by MySQL.
Example, you want to get users end dates (7-day licence after registration):

SELECT `register_date`, `uid`, 
DATE_ADD(`register_date`, INTERVAL 7 DAY) AS `end_date` 
FROM `...`
WHERE...

Perfect, now you can show for user that his licence if over `end_date`…
In addition, you can subtract the required number of days
Continue reading How to add one/two/n days or hours to date in MySQL (DATE_ADD in MYSQL)

Yii 2 database field types

The following abstract column types are supported:

  • pk : an auto-incremental primary key type, will be converted into “int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY”
  • bigpk: an auto-incremental primary key type, will be converted into “bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY”
  • string: string type, will be converted into “varchar(255)”
  • text: a long string type, will be converted into “text”
  • smallint: a small integer type, will be converted into “smallint(6)”
  • integer: integer type, will be converted into “int(11)”
  • bigint: a big integer type, will be converted into “bigint(20)”
  • boolean: boolean type, will be converted into “tinyint(1)”
  • float: float number type, will be converted into “float”
  • decimal: decimal number type, will be converted into “decimal”
  • datetime: datetime type, will be converted into “datetime”
  • timestamp: timestamp type, will be converted into “timestamp”
  • time: time type, will be converted into “time”
  • date: date type, will be converted into “date”
  • money: money type, will be converted into “decimal(19,4)”
  • binary: binary data type, will be converted into “blob”

 
Continue reading Yii 2 database field types