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

[Vovan-VE]

ColumnSchemaBuilder, which is new in 2.0.6, can also be changed to creare single column FK like following:

$this->createTable('child', [
    ...
    'parent_id' => $this->integer()->notNull()
                        ->foreignKey('parent', 'id', 'RESTRICT', 'CASCADE'),
    ...
]);

[sergeymakinen]

I’m working on PRs to solve this. Part 1 is about getting info about constraints (some info is already available in Yii, some is partially available and some is absent) across all supported DBs. Part 2 is about a high level interface (similar to Phinx - at the moment). I plan to maintain BC, so no pain is expected. :) No ETA yet.

[sergeymakinen]

Current status:

1. Part 1:
   • SQL is ready
   • API is ready
   • Tests are 80% ready
   • Implemented on MySQL, tests pass
2. Part 2:
   • Under construction. :)

I expect 1—2 weeks to finish part 1 and make a PR depending my job’s schedule.

[sergeymakinen]

Part 1 has arrived.

[sergeymakinen]

Status update on part 2:

1. SQL is ready
2. API is ready
3. Tests are 70% ready, about 1700 new tests ATM 😄

I expect 1–3 weeks to polish tests. Stay tuned. ;)

[sergeymakinen]

Status update 2 on part 2:

1. I was busy. :(
2. Everything is okay.
3. I aim for a PR in 2 weeks.

[Tigrov]

Closed in favour of #1