one-to-many not create innodb database relationship

[christiancannata]

Hi,
when I set up my database with related entities, it create entity relation fields correctly, but it not create inno-db with foreign keys.

Christian

[ikkez]

It does not handle any foreign keys (yet). Since Cortex is also an Object Document Mapper and supports Jig and MongoDB which do not have something like foreign keys, I had to focus on implementing update and delete cascades model- and event-based anyways. So currently there is no fine-tuning with foreign keys for sql-only usage.

[exodus4d]

I made a small extension for "Foreign Key Constraints" in MySql.
It is basically a Schema Builder "on top of" f3-schema-builder.
Source: Gist

It is not finished, but some functions are already available

Usage:

$schema = new SQL\Schema($db);
$tableModifier= new MySQL\TableModifier($tableName, $schema);

// Let´s start with the common ways of setting up columns in "f3-schema-builder"
// either this way ...
$col = new MySQL\Column($columnName, $tableModifier);
$col->type_tinyint()->nullable(false)->index()
$tableModifier->addColumn($col);
$tableModifier->build();

// ... or like this ...
tableModifier->addColumn('prize')->type($schema::DT_DECIMAL);
$tableModifier->build();

// ... or updating an existing column ...
$col = new MySQL\Column($columnName, $tableModifier);
$col->copyfrom($columnFieldConfig);
$tableModifier->updateColumn($columnName, $col);
$tableModifier->build();

// In any of the above ways you can now add Constraints like this
$constraint = $col->newConstraint($constraintData);
$col->addConstraint($constraint);
... add more constraints if you want :)
$tableModifier->updateColumn($columnName, $col);
$tableModifier->build();

// you can check for existing constraints of a column like this...
$col->constraintExists($constraint)
// ... or  list all constraints of a table ....
$constraints = $tableModifier->listConstraint();
// ... or filter all constraints in a table by a given constraint
$constraints = $tableModifier->listConstraint($constraint);
// ... or drop a constraint ....
$tableModifier->dropConstraint($constraint);

The $constraintData is basically an array with all the required values:

$constraintData = [
  'table' => 'TABLE_NAME, // referenced table name
  'column' => [  // referenced colum names (optional, default: 'id')
      'COL_NAME1', 
      'COL_NAME2', ... ],
  'on_delete' => 'CASCADE', // optional (values: CASCADE, RESTRICT, SET NULL, NO ACTION)
  'on_update' => 'CASCADE' // optional (values: CASCADE, RESTRICT, SET NULL, NO ACTION)
]

The referenceTableName and referenceColName are automatically added.

In my usecase, i have all the constraint information in the $fieldConf in each model class (see below). You have to make sure, the foreign Table and Columns already exists, when setting up Foreign Keys, therefore I add them direct after the ::setup() call for each model.

class UserApiModel extends BasicModel {

    protected $table = 'user_api';

    protected $fieldConf = [
        'active' => [
            'type' => Schema::DT_BOOL,
            'nullable' => false,
            'default' => true,
            'index' => true
        ],
        'userId' => [
            'belongs-to-one' => 'Model\UserModel',
            'type' => Schema::DT_INT,
            'index' => true,
            'constraint' => [
                [
                    'table' => 'user',
                    'on-delete' => 'CASCADE'
                ]
            ]
        ]

        // ....  more columns here
    ];

Foreign Key Constraint names:

The Key name is auto generated and very similar to Cortex naming for column indexes:
L299

e.g. fk_user_api___userId___user___id