Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Alter column size not possible when column used in Constraint [CORE3428] #3790

Open
firebird-automations opened this issue Apr 4, 2011 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: sqldba (sqldba)

Votes: 7

When the column is used in a constraint, altering the column size is not possible. Manually updating the RDB$Fields table also yields the same error.

Example:
CREATE TABLE test_table
(
test_column VARCHAR(25) NOT NULL PRIMARY KEY.
CHECK (test_column <> 'a')
);

After the table is created, you cannot increase the column length because it is used in a constraint. You would have to drop all constraints. This is very difficult if there are several constraints referring to this column. You would also have to recreate all the constraints which is very difficult in a large database.

This issue also applies to domains (this bug defeats the whole purpose of using domains). All other DBMS allow modification of column length even if it is used in a constraint. Just because Firebird [correction from: Firefox] uses it as a index, there is no excuse for this bug.

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Name "all other DBMS". Oracle, for example prohibit changing of column type unless the column contains NULLs only.

@firebird-automations
Copy link
Collaborator Author

Commented by: sqldba (sqldba)

Who said anything about changing column types? I'm talking about column lengths.

What's the purpose of a domain when you have 200+ constraints and then you have to DROP all of them and manually recreate all of them one by one just because the business requirements change and you have to increase the column length? As a sys dba, requirements change all the time and it's a PIA to adapt to them in Firebird. In a production system, this can cause a complete mess.

SQL Server let's you do this. As does Oracle.

I just don't understand why Firebird does not let you increase the column length when it's used in a constraint, have you ever worked on a production system?!?!?

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: When the column is used in a constraint, altering the column size is not possible. Manually updating the RDB$Fields table also yields the same error.

Example:
CREATE TABLE test_table
(
test_column VARCHAR(25) NOT NULL PRIMARY KEY.
CHECK (test_column <> 'a')
);

After the table is created, you cannot increase the column length because it is used in a constraint. You would have to drop all constraints. This is very difficult if there are several constraints referring to this column. You would also have to recreate all the constraints which is very difficult in a large database.

This issue also applies to domains (this bug defeats the whole purpose of using domains). All other DBMS allow modification of column length even if it is used in a constraint. Just because Firefox uses it as a index, there is no excuse for this bug.

=>

When the column is used in a constraint, altering the column size is not possible. Manually updating the RDB$Fields table also yields the same error.

Example:
CREATE TABLE test_table
(
test_column VARCHAR(25) NOT NULL PRIMARY KEY.
CHECK (test_column <> 'a')
);

After the table is created, you cannot increase the column length because it is used in a constraint. You would have to drop all constraints. This is very difficult if there are several constraints referring to this column. You would also have to recreate all the constraints which is very difficult in a large database.

This issue also applies to domains (this bug defeats the whole purpose of using domains). All other DBMS allow modification of column length even if it is used in a constraint. Just because Firebird [correction from: Firefox] uses it as a index, there is no excuse for this bug.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alvaro Castiello (acastiello)

I have exactly the same issue. A varchar based domain mus be increased in size and there´s no
create domain DOMAIN_THAT_NEED_RESIZE_DEF varchar(10);
alter domain DOMAIN_THAT_NEED_RESIZE_DEF type varchar(50);

since it is used in several tables, views, sp it is a real hard work to drop all constraints (and perhaps even views and the so) just to increase the domain size

Regards,
Alvaro Castiello de la Hidalga

@firebird-automations
Copy link
Collaborator Author

Commented by: Leonardt (lxo)

What about this, is there any thought about implementing this?
Especially for changing domains this would be very useful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment