| Subject | Re: [Firebird-general] Suggestion for enhancement |
|---|---|
| Author | Paul Schmidt |
| Post date | 2004-07-29T16:10:32Z |
On Wed, 2004-07-28 at 19:21, GrumpyRain wrote:
need to review all code that uses that field, to see if the software is
compatible with the new use. For example, suppose there is an old
program, it works, but nobody can change it, because it uses some weird
library, that you can't find, and the publisher went out of business.
This program intentionally make use of the NULL on that field, so
removing the NULL will break this program.
What works better is to add the new functionality, and leave the old
field as it is, programs that use the old functionality will continue to
work, programs that need the new, will also work. For example write a
procedure that reads the values of that table that you need, if the
field in question is a NULL, the procedure returns a default value
instead, it passes all other fields as they are. Programs that need the
NULL can have it, and programs that do not need the null, get the
default.
Adding NULL ability to a field is technically easier, but isn't always
allowed, it's not allowed in a primary key, for example. So the code
would need to know about these situations, and return an error in those
cases.
Removing a NULL, means you need to replace it with something, but maybe
not right away, when a record is updated, you could replace the null
with the default value, when a record is read, you could get the default
value and return it. Backup would need to replace the NULL with the
default value, and a Restore would eliminate any NULLs left.
Paul
> It is a bit ambiguous which list I should post this to, but this isThe real issue is that a field has changed how it works, so you really
> the closest I have seen. My apologies if it should be directed
> elsewhere.
>
> I would like to have the ability to set or remove the "NOT NULL"
> requirement on a field using the alter table statement. Currently the
> only way to do it is to create a temporary field, copy the data into
> the temporary field, drop the old field, Recreate the old field using
> the new "NOT NULL" setting, copy the temporary field into the new
> field, drop the temporary field.
>
> Not only is that a long sequence of events that works the DB server
> quite hard, it also means all the Stored Procedures that use that
> field need to be dropped and recreated, and this in turn forces all
> the triggers using those stored procedures to be dropped.
>
need to review all code that uses that field, to see if the software is
compatible with the new use. For example, suppose there is an old
program, it works, but nobody can change it, because it uses some weird
library, that you can't find, and the publisher went out of business.
This program intentionally make use of the NULL on that field, so
removing the NULL will break this program.
What works better is to add the new functionality, and leave the old
field as it is, programs that use the old functionality will continue to
work, programs that need the new, will also work. For example write a
procedure that reads the values of that table that you need, if the
field in question is a NULL, the procedure returns a default value
instead, it passes all other fields as they are. Programs that need the
NULL can have it, and programs that do not need the null, get the
default.
Adding NULL ability to a field is technically easier, but isn't always
allowed, it's not allowed in a primary key, for example. So the code
would need to know about these situations, and return an error in those
cases.
Removing a NULL, means you need to replace it with something, but maybe
not right away, when a record is updated, you could replace the null
with the default value, when a record is read, you could get the default
value and return it. Backup would need to replace the NULL with the
default value, and a Restore would eliminate any NULLs left.
Paul