Firebird, change length of Varchar Primary key column?

[A.M. Hoornweg 144]

Hello all,

 

Are there any FB experts here? I could use a little help ...

 

I need to enlarge the size of a Varchar primary key column in a small but populated Firebird table from 25 to 40 characters. Much to my surprise, that isn't trivial at all.   There are no foreign keys in other tables pointing to this primary key so really, it should be straightforward, shouldn't it?   

 

The table name is "channels" and the primary key column is "paramname", it was originally declared as "varchar(25) character set iso8859_1 not null primary key".

So far my trial steps were:  (note that I did a commit after each statement)

 

//1-Create a temporary column to hold the original data

ALTER TABLE channels add temp_paramname VARCHAR(40) CHARACTER SET ISO8859_1

 

//2-Copy the data

UPDATE channels set temp_paramname=paramname

 

//3-Delete the old column
ALTER TABLE channels DROP paramname

 

// 4-Re-create column "paramname".  The table has records, so I cannot declare it "not null" or "primary key" at this point 
ALTER TABLE channels add paramname VARCHAR(40) CHARACTER SET ISO8859_1

 

//5-Populate it with the original data

UPDATE channels set paramname=temp_paramname

//6-Now make it primary key - this step fails!

alter table channels add primary key (paramname)

 

//7-delete the temp column now...

 

 

 

 

I'd be grateful for any help.  

[Guest]

It would help if you supply the error message. I have a theory, but it does not compute 100%, and it's not creative to speculate.

Also, do you use Isql or an admin tool? I believe that my too "helps me" with step 4. That meaning it will run a series of DDL queries.

 

[Remy Lebeau 1392]

6 hours ago, A.M. Hoornweg said:

I need to enlarge the size of a Varchar primary key column in a small but populated Firebird table from 25 to 40 characters. Much to my surprise, that isn't trivial at all.   There are no foreign keys in other tables pointing to this primary key so really, it should be straightforward, shouldn't it?

You can increase the column size inline without making a copy and dropping the original.  Firebird allows columns to be altered with minimal overhead if the same type is used and just the size is increased.  Modifications to an existing column will be disallowed if existing data would be lost/truncated, but that is not the case when increasing the size.

 

However, a PRIMARY KEY cannot be altered, so you have to drop the key first.

 

http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-altraltrcol

 

Quote

With the ALTER [COLUMN] clause, attributes of existing columns can be modified without the need to drop and re-add the column. Permitted modifications are:

• change the name (does not affect the metadata change counter)
• change the data type (increases the metadata change counter by one)
• change the column position in the column list of the table (does not affect the metadata change counter)
• delete the default column value (does not affect the metadata change counter)
• set a default column value or change the existing default (does not affect the metadata change counter)
• change the type and expression for a computed column (does not affect the metadata change counter)

...

Changing the Data Type of a Column: the TYPE Keyword

The keyword TYPE changes the data type of an existing column to another, allowable type. A type change that might result in data loss will be disallowed. As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

 

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

 

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

Try something like this (untested):

ALTER TABLE channels
DROP CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT>,
ALTER COLUMN paramname TYPE VARCHAR(40) CHARACTER SET ISO8859_1,
ADD CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT> PRIMARY KEY (paramname);

If you didn't give your primary key constraint an explicit name originally, you can use the system tables to find the auto-generated name that Firebird created (it is in the RDB$CONSTRAINT_NAME field in the RDB$RELATION_CONSTRAINTS system table, see Find all column names that are primary keys in Firebird database).

[A.M. Hoornweg 144]

19 hours ago, Dany Marmur said:

It would help if you supply the error message. I have a theory, but it does not compute 100%, and it's not creative to speculate.

Also, do you use Isql or an admin tool? I believe that my too "helps me" with step 4. That meaning it will run a series of DDL queries.

 

Hi Dany,

the error message said that FB couldn't create the primary key because the column wasn't declared as "not null".   Which I couldn't do, because the new column contains nulls right after creation in step 4. 

I did the steps one by one in FlameRobin, but when everything works I must re-code it either in Delphi or in InnoSetup (because I have to distribute it as part of a software update).

[A.M. Hoornweg 144]

13 hours ago, Remy Lebeau said:

You can increase the column size inline without making a copy and dropping the original.  Firebird allows columns to be altered with minimal overhead if the same type is used and just the size is increased.  Modifications to an existing column will be disallowed if existing data would be lost/truncated, but that is not the case when increasing the size.

 

However, a PRIMARY KEY cannot be altered, so you have to drop the key first.

 

http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-altraltrcol

 

Try something like this (untested):

ALTER TABLE channels
DROP CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT>,
ALTER COLUMN paramname TYPE VARCHAR(40) CHARACTER SET ISO8859_1,
ADD CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT> PRIMARY KEY (paramname);

If you didn't give your primary key constraint an explicit name originally, you can use the system tables to find the auto-generated name that Firebird created (it is in the RDB$CONSTRAINT_NAME field in the RDB$RELATION_CONSTRAINTS system table, see Find all column names that are primary keys in Firebird database).

Thanks Remy, I'll give it a try and keey you updated!

[A.M. Hoornweg 144]

16 hours ago, Remy Lebeau said:

You can increase the column size inline without making a copy and dropping the original.  Firebird allows columns to be altered with minimal overhead if the same type is used and just the size is increased.  Modifications to an existing column will be disallowed if existing data would be lost/truncated, but that is not the case when increasing the size.

 

However, a PRIMARY KEY cannot be altered, so you have to drop the key first.

 

http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-altraltrcol

 

Try something like this (untested):

ALTER TABLE channels
DROP CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT>,
ALTER COLUMN paramname TYPE VARCHAR(40) CHARACTER SET ISO8859_1,
ADD CONSTRAINT <NAME_OF_YOUR_PRIMARY_KEY_CONTRAINT> PRIMARY KEY (paramname);

If you didn't give your primary key constraint an explicit name originally, you can use the system tables to find the auto-generated name that Firebird created (it is in the RDB$CONSTRAINT_NAME field in the RDB$RELATION_CONSTRAINTS system table, see Find all column names that are primary keys in Firebird database).

Thanks a lot Remy, this works indeed!  But I needed to perform a commit after each step, else it threw an error.

[Remy Lebeau 1392]

9 hours ago, A.M. Hoornweg said:

the error message said that FB couldn't create the primary key because the column wasn't declared as "not null".   Which I couldn't do, because the new column contains nulls right after creation in step 4. 

You can create the new column with NULL as its default value, then populate the column with data as needed, and then alter the column to drop the default value, and update the system tables to mark the column as "not null".  However, none of this is necessary if you don't drop the original column to begin with.