Hello there, as the username suggest I am novice with codeigniter. I posted this originally on stackoverflow, and people there redirected me to the forums. Please see that post
here.
Contents from that post:
I am trying to create a migration in CodeIgniter 4 and it keeps failing due to the same issue. I tried to run the - to my understanding - equivalent query in MySQL and that one worked without problems.
2024-03-21-091038_CreateCartTable.php migration file:
Code:
class CreateCartTable extends Migration
{
public function up()
{
$this->forge->addField([
'id' => [
'type' => 'INT',
'unsigned' => true,
'auto_increment' => true,
'null' => false,
],
'productCode' => [
'type' => 'VARCHAR',
'constraint' => '15',
'null' => false,
],
'quantity' => [
'type' => 'INT',
'default' => 1,
'null' => false,
],
'customerNumber' => [
'type' => 'INT',
'null' => false,
],
'createdAt' => [
'type' => 'TIMESTAMP',
'default' => 'CURRENT_TIMESTAMP',
],
'updatedAt' => [
'type' => 'TIMESTAMP',
'default' => 'CURRENT_TIMESTAMP',
'onUpdate' => 'CURRENT_TIMESTAMP',
],
]);
$this->forge->addKey('id', true);
$this->forge->addForeignKey('customerNumber', 'customers', 'customerNumber');
$this->forge->addForeignKey('productCode', 'products', 'productCode');
$this->forge->createTable('cart', true);
}
public function down()
{
$this->forge->dropTable('cart', true);
}
}
Running this migration results in the following:
Code:
$ php spark migrate:refresh
CodeIgniter v4.4.6 Command Line Tool - Server Time: 2024-03-21 12:52:32 UTC+00:00
Rolling back migrations to batch: 0
Rolling back: (App) 2024-03-14-135404_App\Database\Migrations\AddOfficeLatLon
Rolling back: (App) 2024-03-14-111807_App\Database\Migrations\AddAboutToOffices
Done rolling back migrations.
Running all new migrations...
[CodeIgniter\Database\Exceptions\DatabaseException]
Referencing column 'productCode' and referenced column 'productCode' in foreign key constraint 'cart_productCode_foreign' are incompatible.
at SYSTEMPATH/Database/BaseConnection.php:647
Caused by:
[CodeIgniter\Database\Exceptions\DatabaseException]
Referencing column 'productCode' and referenced column 'productCode' in foreign key constraint 'cart_productCode_foreign' are incompatible.
at SYSTEMPATH/Database/MySQLi/Connection.php:311
Caused by:
[mysqli_sql_exception]
Referencing column 'productCode' and referenced column 'productCode' in foreign key constraint 'cart_productCode_foreign' are incompatible.
at SYSTEMPATH/Database/MySQLi/Connection.php:306
MySQL Query:
Code:
CREATE TABLE classicmodels.cart (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
productCode VARCHAR(15) NOT NULL,
quantity INT DEFAULT 1 NOT NULL,
customerNumber INT NOT NULL ,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT FOREIGN KEY (customerNumber) REFERENCES customers(customerNumber),
CONSTRAINT FOREIGN KEY (productCode) REFERENCES products(productCode)
);
Running this query results in the following:
Code:
Query OK, 0 rows affected (0.04 sec)
I tried to run the migration several times and used several variations to insert the data, I always get the same error message: Referencing column 'productCode' and referenced column 'productCode' in foreign key constraint 'cart_productCode_foreign' are incompatible. I have read the CodeIgniter documentation for the forge class and migrations, if I did not miss anything, I am doing everything according to protocol. I also looked on previous posts here on stackoverflow, none of the issues seem to be relevant to mine, I am thinking I might just be missing a trivial point and my usage of the forge class in incomplete or incorrect.
I also tried commenting out the fields createdAt updatedAt and the line with the productCode constraint. The first two I removed, because I was getting this error: Invalid default value for 'createdAt'. Now the migration works just fine. Also I am using Ubuntu 22.04.4 LTS, and the codeigniter console tells me the current time is 2024-03-21 14:21:10 UTC+00:00 even though I am in an UTC+01:00 timezone, and it is currently 15:21. Maybe this is also relevant.
