Better constraints management in migrations

arogachev commented 9 years ago

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 commented 9 years ago

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'),
    ...
]);