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)