FB 4.01 - Error adding not null fields

[Dany40]

Hello;

With FB 4.01, running the next statements for adding a not null field is not possible:

ALTER TABLE PROC ADD SENT Char(8) NOT NULL;
COMMIT;
UPDATE PROC SET SENT = ' ' WHERE SENT IS NULL;

I think that with 4.0 it was running ok (99% sure) but I tested with 2.5 and it works fine (100% sure).

I don't see a fix for this in 4.02.

Thank you!

[Martijn Tonies (Upscene Productions)]

Hi,

 

I think if you use a DEFAULT for the column, it should work.

 

Alternatively, add it is a NULLable field, set a value and then set it to NOT NULL.

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/edfc99fe-4d03-48e8-8ccd-7b9727b69dfan%40googlegroups.com.

[Karol Bieniaszewski]

Hi

 

If you add not null field

1. you must set default value
2. or add field nullable run update on it to setup some value. And then change it to not null.

 

Regards,

Karol Bieniaszewski

--

[Ertan Küçükoglu]

Hi,

FirebirdSQL 4.x does not allow you to add not null columns when there is existing data in the table. It was allowed in Firebird 2.5.x.

You either provide a default value 

or 

You first add that column as nullable. Then populate it with data. Then alter that column to not null.

Dany40 <dgsc...@gmail.com>, 19 Eyl 2022 Pzt, 16:12 tarihinde şunu yazdı:

[Dimitry Sibiryakov]

Dany40 wrote 19.09.2022 15:12:
> With FB 4.01, running the next statements for adding a not null field is not
> possible:
>
> ALTER TABLE PROC ADD SENT Char(8) NOT NULL;

This statement broke database referential integrity and caused unrestorable
backup. Naturally it is prohibited.
Special exceptions may be specified default value for this field or empty table.

--
WBR, SD.

[Mark Rotteveel]

This is not a bug, this is an intentional change introduced in Firebird
3.0. Adding a NOT NULL column or altering a column to NOT NULL
(supported since Firebird 3.0) will enforce that constraint immediately.

Either add the column without NOT NULL, populate it and alter the column
to NOT NULL, or create it as NOT NULL with a default value.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

See also the Firebird 4.0 Language Reference for ALTER TABLE:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-tbl-altradd

Mark
--
Mark Rotteveel

[Dany40]

Thank you to all who answered me. Following your suggestions I find this solution:

ALTER TABLE PROC ADD SENT CHAR(8) DEFAULT ' ' NOT NULL;
ALTER TABLE PROC ALTER COLUMN SENT POSITION 9;
COMMIT;
ALTER TABLE PROC ALTER COLUMN SENT DROP DEFAULT;
COMMIT;

It seems to work. BTW, when I checked the results a few selects, it was  weird to see that select * from PROC where SENT = ''        ' (8 spaces) is returning all the records in the table, but the default was just ' ' (1 space).

Best regards!

[Tim Crawford]

Ah  yes.  Something I did not realize after literally years of using SQL and found out a couple of years ago...
In my defence I never had to deal with free text in char/varchar and when I did it doesn't matter
The SQL standard says you ignore trailing blanks on strings (char or varchar)
That might not be 100% correct way to phrase it, but close enough I think

All of the below return one row.

select 'Empty equals 4 blanks!' , cast('' as varchar (4) ) as empty
from RDB$DATABASE
where cast('' as varchar(10) ) = '    '

select '4 blanks = empty!' , cast('    ' as varchar (4) ) as empty
from RDB$DATABASE
where cast('    ' as varchar(10) ) = ''

select '4 blanks = 1 blank!' , cast('    ' as varchar (4) ) as empty
from RDB$DATABASE
where cast('    ' as varchar(10) ) = ' '

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/97746eef-83c9-46e3-9f55-95e2eaa4e8f9n%40googlegroups.com.

[Mark Rotteveel]

On 19-09-2022 19:43, Dany40 wrote:
> Thank you to all who answered me. Following your suggestions I find this
> solution:
>
> ALTER TABLE PROC ADD SENT CHAR(8) DEFAULT ' ' NOT NULL;
> ALTER TABLE PROC ALTER COLUMN SENT POSITION 9;
> COMMIT;
> ALTER TABLE PROC ALTER COLUMN SENT DROP DEFAULT;
> COMMIT;
>
> It seems to work. BTW, when I checked the results a few selects, it
> was  weird to see that select * from PROC where SENT = ''        ' (8
> spaces) is returning all the records in the table, but the default was
> just ' ' (1 space).

There are two things at play here:
1) a (non-NULL) CHAR is always padded with spaces up to its declared, so
setting an empty or 1-space string on a CHAR(8) will produce a value
with 8 spaces.
2) equality of (non-NULL) string values (CHAR or VARCHAR) ignores
trailing spaces (or trailing 0x00 (NUL) bytes for OCTETS/BINARY).

Mark
--
Mark Rotteveel