Modeling MySQL views and primary keys

[Tad <ta...@gmail.com>]

We've got an existing MySQL 5.x database that contains a single view. This
view includes the primary key column of its source table in its results.

When modeling this view with a db-entity (through re-engineering,
migration, and manual creation) the modeler tells us that the entity lacks
a primary key column (even though the modeler picks up the column and adds
it as an attribute). So, we assign the appropriate PK column, and this
error disappears.

The problem now is that any future migrations attempt to alter the view and
add a primary key (using ALTER TABLE, no less), and we have to know not to
apply these changes to the database schema.

Is there a way to suppress this behavior? Perhaps by ignoring the view
entirely, or adding something to the view definition itself to assign the
"primary key" attribute to a column of its results?

Thanks for any help.

-Tad Fisher

Re: Modeling MySQL views and primary keys

[Andrus Adamchik <an...@objectstyle.org>]

AFAIK there's no way to tell the Modeler to ignore certain manual model tweaks when creating migrations. Things like that are the reason why I am using manual SQL-based migrations to evolve my schema. 

Andrus

On Feb 4, 2012, at 3:11 AM, Tad wrote:

> We've got an existing MySQL 5.x database that contains a single view. This
> view includes the primary key column of its source table in its results.
> 
> When modeling this view with a db-entity (through re-engineering,
> migration, and manual creation) the modeler tells us that the entity lacks
> a primary key column (even though the modeler picks up the column and adds
> it as an attribute). So, we assign the appropriate PK column, and this
> error disappears.
> 
> The problem now is that any future migrations attempt to alter the view and
> add a primary key (using ALTER TABLE, no less), and we have to know not to
> apply these changes to the database schema.
> 
> Is there a way to suppress this behavior? Perhaps by ignoring the view
> entirely, or adding something to the view definition itself to assign the
> "primary key" attribute to a column of its results?
> 
> Thanks for any help.
> 
> -Tad Fisher