tbMy - how to make a field longer?

[Nicole]

I have a table, which looks like shown below.
It is embedded in my code and to touch it in the wrong way, will kill a lot of items, I do not want to imagine what all.

There is a field
"BEZEICHNUNG VARCHAR(50) "

I would rather prefer to have it as
100 digits
and
UTF8 or so.

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBNEWS
2. (
3.   ID INTEGER NOT NULL,
4.   DATUM DATE NOT NULL,
5.   ZEIT TIME,
6.   CODE_ INTEGER NOT NULL,
7.   BEZEICHNUNG VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE DE_DE,
8.   CONSTRAINT PK_TBNEWS PRIMARY KEY (ID)
9. );
10.  
11. ALTER TABLE TBNEWS ADD CONSTRAINT FK_TBNEWS_1
12.   FOREIGN KEY (DATUM) REFERENCES TBKALENDER (JDATUM);
13. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
14.  ON TBNEWS TO  SYSDBA WITH GRANT OPTION;

And yes, this fields arel filled with stuff already.

Do you see any option to change the length or even the type without data loss and being busy for half a day?
Or is it better to live with the old version?

Thank you for your ideas.

[dsiders]

I have a table, which looks like shown below.
It is embedded in my code and to touch it in the wrong way, will kill a lot of items, I do not want to imagine what all.

There is a field
"BEZEICHNUNG VARCHAR(50) "

I would rather prefer to have it as
100 digits
and
UTF8 or so.

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBNEWS
2. (
3.   ID INTEGER NOT NULL,
4.   DATUM DATE NOT NULL,
5.   ZEIT TIME,
6.   CODE_ INTEGER NOT NULL,
7.   BEZEICHNUNG VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE DE_DE,
8.   CONSTRAINT PK_TBNEWS PRIMARY KEY (ID)
9. );
10.  
11. ALTER TABLE TBNEWS ADD CONSTRAINT FK_TBNEWS_1
12.   FOREIGN KEY (DATUM) REFERENCES TBKALENDER (JDATUM);
13. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
14.  ON TBNEWS TO  SYSDBA WITH GRANT OPTION;

And yes, this fields arel filled with stuff already.

Do you see any option to change the length or even the type without data loss and being busy for half a day?
Or is it better to live with the old version?

Thank you for your ideas.

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-tbl-alter

[Nicole]

Thank you for your answer.
Unfortunately this is just what is generally known about how to change a table.

My questions was: Is it wise to do it in my case and if yes how exactly?

[paweld]

if you only want to change varchar length, use ALTER COLUMN:

Code: SQL  [Select][+][-]

1. ALTER TABLE TBNEWS ALTER COLUMN BEZEICHNUNG TYPE VARCHAR(100);

 
while if you want to change the CHARSET as well, you need to add a new column and move the data to it

Code: SQL  [Select][+][-]

1. ALTER TABLE TBNEWS ADD BEZEICHNUNG_NEW VARCHAR(100) CHARACTER SET UTF8;  
2. UPDATE TBNEWS SET BEZEICHNUNG_NEW=CAST(BEZEICHNUNG AS VARCHAR(100) CHARACTER SET UTF8);  
3. ALTER TABLE TBNEWS DROP BEZEICHNUNG;  
4. ALTER TABLE TBNEWS ALTER COLUMN BEZEICHNUNG_NEW TO BEZEICHNUNG;

 

[Nicole]

Thanks Pawel, this looks very helpful!