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)

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

How to get last insert id after insert in Joomla database?

Get last id
Joomla solution for get last insert ID after insert a new row in J! database:

$db->insertid();

Example ↓

$query = $db->getQuery(true);
$query->insert('#__test_table');
$query->set('`user_id`='.(int)$uid);
$db->setQuery($query);
$db->query();
$last_id = $db->insertid();

In native PHP ↓
Object oriented style

$last_id = $mysqli->insert_id;

Continue reading How to get last insert id after insert in Joomla database?