Better constraints management in migrations

[arogachev]

Currently we have to specify foreign key name with both adding and dropping.

$this->addForeignKey(
    'tests_questions_test_id_fkey',
    'tests_questions',
    'test_id',
    'tests',
    'id',
    'CASCADE',
    'CASCADE'
);

$this->dropForeignKey('tests_questions_test_id_fkey', 'tests_questions');

Some DBMS like MySQL generate it automatically, but the dropping part for me is more frustrating. You have to look through all previous migrations and find the name of corresponding foreign key. One solution will be following some convention and ask team members to follow it too, but it will be great if:

1) Foreign key names will be generated automatically so we can just write this:

$this->addForeignKey(
    'tests_questions',
    'test_id',
    'tests',
    'id',
    'CASCADE',
    'CASCADE'
);

2) And for the dropping part we can specify relations something like that:

$this->dropForeignKey('tests_questions', ['test_id' => 'tests.id']);

And foreign key will be found and dropped automatically.

That way developer only cares about relations and not names.

I think It can be applied to primary keys, indices, etc. too.

What do you think? Is it possible with DBMS?

Maybe use some naming convention if DBMS don't support auto generation? In this case we can build constraint name string depending on relations and add / drop it.

Funding

• You can sponsor this specific effort via a Polar.sh pledge below
• We receive the pledge once the issue is completed & verified

[arogachev]

Automatic generation can be applied for example if the constraint name is skipped, so user can choose between manual / automatical handling of it.

Example with users and messages tables.

$this->addForeignKey(
    null,
    'messages',
    'user_id',
    'users',
    'id',
    'CASCADE',
    'CASCADE'
);

Generated foreign key name will be for example: messages_user_id-users-id_fk.

Dropping:

$this->dropForeignKey(null, 'messages', ['id' => 'users.id']);

We can retrieve this string messages_user_id-users-id_fk based on passed relations and drop corresponding foreign key.

[arogachev]

Seems like this concept already used in Phinx. Not sure about their implementation of it.

[arogachev]

And in Phinx relations are not even specified for dropping foreign keys, column name is enough.

[arogachev]

Also similar concept is used in Laravel (for dropping you need to specify string though).

[nineinchnick]

I like the idea. I'm using PostgreSQL and it builds constraint names from it's definition, like table name and column names for FK and index constraints, adding _fkey or _idx at the end. But there's a 63 character limit for name length, so that could be an issue.

Other DBMS need to be checked about this.

[unclead]

:+1:

[omnilight]

Absolutely aggree, currently it is a pain to write foreight keys every time

[samdark]

The issue is that each DBMS names its keys differently so I would not rely on it. Personally I'm using idx-table-my_column1-my_column2 and fk-table-column_1-foreign_table-foreign_column.

[samdark]

What worries me is that there could be an index for multiple columns and there could be multiple indexes of different types for a single column.

[samdark]

Many foreign keys for a single column happens daily.

[arogachev]

OK, so it's better to use uniform naming convention instead. But as @nineinchnick said, DBMS need to be checked for max length limitations etc.

[arogachev]

@samdark Could you provide an example of multiple foreign keys?

[samdark]

That's morning sleepy typo. Of course, I've meant indexes.

[TerraSkye]

you could overide it yourself for this particular case and let it auto generate. its the same i did, altough i have a function for the keys.

at the end off the UP i add all the fk's with auto generated names.

at the beginning of the "down" i remove the keys by the using the same configuration. thus the same way its build.

[arogachev]

Of course we can override and implement by ourselves, but I personally expect this feature out of the box.

[TerraSkye]

the problem with that is that project that move to Yii with an existing database cant use that feature. even better would be that u could specify the markup yourself ea : "FK"- {basteTable}-{columns}-{reftable}-{columns}

[arogachev]

Some kind of migration mechanism can be implemented. Alternatively users can continue to use existing algorithm (I described it above).

[samdark]

https://github.com/yiisoft/yii2/pull/13018

[samdark]

https://github.com/yiisoft/db-migration/issues/5 seems the issue was incorrectly split when moving.