> I suppose you did commit the CREATE DOMAIN statement first?

Yes.

> I hope that "practically impossible" doesn't indicate that you're

trying to this change while the database is online.

Practically impossible means that the field is referenced in dozens of
views and stored procedures, which then seem to be referenced
themselves. Each of these little fleas have lesser fleas, and so ad
infinitum, and each is biting (to butcher Augustus de Morgan).

> The only thing that's not being changed here is the length.

The character set isn't changing either. Only the check constraint.

> As a side-issue, I'd also be leery about having foreign key

dependencies on a primary column that has only four possible values.

It's not a FK, but I'd wouldn't hesitate to use an FK with lousy
selectivity.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Friday, February 17, 2006 5:44 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Can't alter column type

At 04:53 AM 18/02/2006, you wrote:

>Thanks to Simon for showing me how to find the remaining dependencies.
>Strange, I got back eight rows and the error complains of six items.
>
>That said, why is the column being deleted instead of being altered?

You have multiple exceptions being thrown. They come through to the
client in an array and you get them in reverse order:

The last one was
335544351 |no_meta_update|Unsuccessful metadata update

The one before that was
|335544673 |no_delete|Cannot delete (a generalisation of multiple
reasons for not being able to modify or delete a column)

And the first one was
335544630 |dependency|There are %ld dependencies

But "alter column <x> type <y>" won't delete data. It's more likely
to be a case where a mix of similar exceptions is being rolled into
one exception code.

Also make sure that the client and the message file are the ones that
belong to the server.


I suppose you did commit the CREATE DOMAIN statement first?

>The underlying data type is not changing.

But the attributes of (rules for) the data are changing. Things like
character sets and check constraints affect existing data. The only
thing that's not being changed here is the length.

> Fixing the other dependencies may be practically impossible.

Well, it's theoretically possible! I hope that "practically
impossible" doesn't indicate that you're trying to this change while
the database is online....The *purpose* of referential integrity
constraints is, above all, to keep declared relationships
consistent. You are changing the rules for a column on which
dependencies are based. That would interfere with foreign keys and
anything you might have declared for that column or its dependents in
an SP or trigger.

So, to change the rules and maintain consistency, you'll have to drop
the constraints, fix the dependent columns, fix the primary column
and alter the SPs and triggers, in that order. Then, in the right
order, reimpose the constraints. You can't do any of this stuff
while the database is online.

I hope, too, that you have done a full offline backup before you
started tinkering. Better still would be if you did that offline
backup then restored it to a new database and did your tinkering on
that. (Keeping the live database offline, natch!!) You'd probably
find the inconsistency between the stored dependencies and the
exception report would have disappeared in the restored version.

As a side-issue, I'd also be leery about having foreign key
dependencies on a primary column that has only four possible
values. But since you already have them, I guess you're happy about it.

./heLen



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links