Subject AW: [firebird-support] Changing Columns
Author checkmail

.. or is there an option to export all data from the database without database itself, then I can change the ddl, create a clean database and import the data again? If I do it manually, I must show in every table if is there a relation in another one.




Von: []
Gesendet: M
ontag, 6. Juli 2015 08:03
Betreff: AW: [firebird-support] Changing Columns



Hello Ann,


Thank you, but it doesn’t work. Neither the primary table I can change nor a referenced table.



This operation is not defined for system tables.

unsuccessful metadata update.

Column TEILENR from table TBESTPOS_1 is referenced in TBESTPOS_1_BU.





This operation is not defined for system tables.

unsuccessful metadata update.

Column TEILENR from table TTEILE is referenced in CHECK_85.




Von: []
Gesendet: Freitag, 3.
Juli 2015 16:51
Betreff: Re: [firebird-support] Changing Columns



On Jul 3, 2015, at 8:09 AM, 'checkmail' check_mail@... [firebird-support] <> wrote:


I would like to change a primary column in my database from 8 to 16 characters. Over 100 Tables are constrained, foreign key reference. Now I can change the DDL from the Database and create this again, but I must import all Data-Tables. An other Option is the changing manually, delete all references, changing and create.

 Is there another option? Can I change it R 20;offline” without the necessity to import all data or change it manually?



If you defined a domain, just change the domain, using a SQL ALTER DOMAIN statement.  If not, use ALTER TABLE.  Firebird will do all the necessary magic.  Indexes on strings are independent of the length of the string - up to a limit and at 16 characters you're nowhere near the limit.  Strings are comparable regardless of length.   


As for the actual data, after you've changed the columns, Firebird will return those columns as 16 characters and accept up to 16 characters when you store or update the records.  That completely hides the fact that the previously stored data doesn't change.   New records and records that have been modified will use the 16 character format.  Old records will continue to use the 8 character format.


The source of the magic is the "format version number" which every record carries.  When a table is createed or altered in a way that changes its stored form, Firebird generates a format version that describes the physical structure of the record.  During compilation, every statement picks up the current format version for the tables it uses.  When Firebird reads a record with an older format version, it performs the necessary transformations.