How about add fk from user_assignment.user_id to user.id?

[bscheshirwork]

What steps will reproduce the problem?

We have 2 different table pack in one place. Just connect it.

What is the expected result?

Create fk after join dektrium packages

What do you get instead?

not implemented

[bscheshirwork]

So... We can add migration like this

migration order: after add index i.e. m170908_000010_add_rbac_fk.php

namespace Da\User\Migration;

use yii\base\InvalidConfigException; use yii\db\Migration; use yii\rbac\DbManager; use Yii;

class m170908_000010_add_rbac_fk extends Migration {

/**
 * @throws yii\base\InvalidConfigException
 * @return DbManager
 */
protected function getAuthManager()
{
    $authManager = Yii::$app->getAuthManager();
    if (!$authManager instanceof DbManager) {
        throw new InvalidConfigException('You should configure "authManager" component to use database before executing this migration.');
    }

    return $authManager;
}

public function safeUp()
{
    $authManager = $this->getAuthManager();
    $this->db = $authManager->db;

    $restrict = MigrationHelper::isMicrosoftSQLServer($this->db->driverName) ? 'NO ACTION' : 'RESTRICT';

    $this->addForeignKey('fk_auth_assignment_user', $authManager->assignmentTable, 'user_id', '{{%user}}', 'id', 'CASCADE', $restrict);
}

public function safeDown()
{
    $authManager = $this->getAuthManager();
    $this->db = $authManager->db;

    $this->dropForeignKey('fk_auth_assignment_user', $authManager->assignmentTable);
}

}

or we can add the instruction to add migration to user's project because we can use two different db for rbac and user

[bscheshirwork]

upd: This is terrible but we have a different type:

create table auth_assignment
(
  item_name  varchar(64) not null,
  user_id    varchar(64) not null,
  created_at int         null,
  primary key (item_name, user_id),
  constraint auth_assignment_ibfk_1
  foreign key (item_name) references auth_item (name)
    on update cascade
    on delete cascade
)
  collate = utf8_unicode_ci;

create index `idx-auth_assignment-user_id`
  on auth_assignment (user_id);

create table user
(
  id                  int auto_increment
    primary key,
  username            varchar(255)           not null,
  email               varchar(255)           not null,
  password_hash       varchar(60)            not null,
  auth_key            varchar(32)            not null,
  unconfirmed_email   varchar(255)           null,
  registration_ip     varchar(45)            null,
  flags               int default '0'        not null,
  confirmed_at        int                    null,
  blocked_at          int                    null,
  updated_at          int                    not null,
  created_at          int                    not null,
  last_login_at       int                    null,
  last_login_ip       varchar(45)            null,
  auth_tf_key         varchar(16)            null,
  auth_tf_enabled     tinyint(1) default '0' null,
  password_changed_at int                    null,
  gdpr_consent        tinyint(1) default '0' null,
  gdpr_consent_date   int                    null,
  gdpr_deleted        tinyint(1) default '0' null,
  constraint idx_user_email
  unique (email),
  constraint idx_user_username
  unique (username)
)
  collate = utf8_unicode_ci;

Join is impossible.

[bscheshirwork]

Also we can not add GENERATED column on auto_increment:

    > add column id_varchar VARCHAR(64) GENERATED ALWAYS AS (CONVERT(`id`, CHAR(64))) to table {{%user}} ...Exception: SQLSTATE[HY000]: General error: 3109 Generated column 'id_varchar' cannot refer to auto-increment column.
The SQL being executed was: ALTER TABLE `user` ADD `id_varchar` VARCHAR(64) GENERATED ALWAYS AS (CONVERT(`id`, CHAR(64))) (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:664)

    > add column id_varchar VARCHAR(64) GENERATED ALWAYS AS (CAST(`id` as CHAR(64))) to table {{%user}} ...Exception: SQLSTATE[HY000]: General error: 3109 Generated column 'id_varchar' cannot refer to auto-increment column.
The SQL being executed was: ALTER TABLE `user` ADD `id_varchar` VARCHAR(64) GENERATED ALWAYS AS (CAST(`id` as CHAR(64))) (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:664)

The solution is a create index...

This migration is work for me:

<?php

use yii\base\InvalidConfigException;
use yii\db\Migration;
use yii\rbac\DbManager;

/**
 * Class m181011_100000_add_user_fks
 * note: must be apply after yii2/rbac/migrations/m170907_052038_rbac_add_index_on_auth_assignment_user_id.php
 */
class m181011_100000_add_user_fks extends Migration
{

    /**
     * @throws yii\base\InvalidConfigException
     * @return DbManager
     */
    protected function getAuthManager()
    {
        $authManager = Yii::$app->getAuthManager();
        if (!$authManager instanceof DbManager) {
            throw new InvalidConfigException('You should configure "authManager" component to use database before executing this migration.');
        }

        return $authManager;
    }

    public function safeUp()
    {
        $authManager = $this->getAuthManager();

        $this->addColumn('{{%user}}', 'id_varchar', $this->string(64)->notNull());
        $this->createIndex('{{%idx_user_id_varchar}}', '{{%user}}', 'id_varchar');

        $this->execute(<<<SQL
CREATE TRIGGER `user_varchar_id_insert` BEFORE INSERT ON `user`
FOR EACH ROW BEGIN
    DECLARE id_varchar VARCHAR(64);
    SET id_varchar = (CAST(NEW.`id` as CHAR(64)));
    SET NEW.`id_varchar` = id_varchar;
END;
SQL
        );
        $this->execute(<<<SQL
CREATE TRIGGER `user_varchar_id_update` BEFORE UPDATE ON `user`
FOR EACH ROW BEGIN
    DECLARE id_varchar VARCHAR(64);
    IF NEW.`id` != OLD.`id` THEN
        SET id_varchar = (CAST(NEW.`id` as CHAR(64)));
        SET NEW.`id_varchar` = id_varchar;
    END IF;
END;
SQL
        );

        $this->execute('UPDATE `user` SET id_varchar = (CAST(`id` as CHAR(64)))');

        $this->addForeignKey('fk_auth_assignment_user', $authManager->assignmentTable, 'user_id', '{{%user}}', 'id_varchar', 'CASCADE', 'CASCADE');
    }

    public function safeDown()
    {
        $authManager = $this->getAuthManager();

        $this->dropForeignKey('fk_auth_assignment_user', $authManager->assignmentTable);

        $this->execute('DROP TRIGGER IF EXISTS `user_varchar_id_update`');
        $this->execute('DROP TRIGGER IF EXISTS `user_varchar_id_insert`');

        $this->dropColumn('{{%user}}', 'id_varchar');
    }
}

note: for create trigger reason I should configure mysql container in my docker composition --log-bin-trust-function-creators=1

mysql:
image: mysql:8.0.12
entrypoint: ['/entrypoint.sh', '--default-authentication-plugin=mysql_native_password', '--log-bin-trust-function-creators=1']

[bscheshirwork]

Hm... This trigger is harder...

CREATE definer=`root`@`localhost` TRIGGER `user_varchar_id_insert` BEFORE INSERT ON `user`
FOR EACH ROW BEGIN
    DECLARE fk_parent_user_id INT DEFAULT 0;
    DECLARE id_varchar VARCHAR(64);

    select `auto_increment` into fk_parent_user_id
    from `information_schema`.`tables`
    where `table_name` = 'user'
    and `table_schema` = database();

    SET id_varchar = (CAST(fk_parent_user_id as CHAR(64)));
    SET NEW.`id_varchar` = id_varchar;
END;

but this is so strong.