Submitted by: Bill Oliver (verbguy)

Is related to QA81

In Helen's book (p. 289), she says that ALTER TABLE DROP COLUMN will fail if the column is "part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint." OK. Here I reproduce this behavior:

SQL> create table pk2 (i1 integer not null, i2 integer);
SQL> alter table pk2 add constraint pk2_pk primary key (i1);
SQL> alter table pk2 drop i1;
Statement failed, SQLCODE = -607

unsuccessful metadata update
-column I1 from table PK2 is referenced in index PK2_PK

SQL> show table pk2;
I1 INTEGER Not Null
I2 INTEGER Nullable
CONSTRAINT PK2_PK:
Primary key (I1)

On the other hand, if you create a table with the PRIMARY KEY definition as part of the create table statement, this is somehow different.

SQL> create table pk1 (i1 integer not null primary key, i2 integer);
SQL> show table pk1;
I1 INTEGER Not Null
I2 INTEGER Nullable
CONSTRAINT INTEG_10:
Primary key (I1)
SQL> alter table pk1 drop i1;
SQL> show table pk1;
I2 INTEGER Nullable

I tried this in FB 1.5, Vulcan, and FB2 and the behavior is the same. Is there a reason why I can drop the PK column in the 2nd example I am not
aware of, or just "that's how it is?"

-----
Dmitry then said:

* The rules for dropping a regular column:
*
* 1. the column is not referenced in any views.
* 2. the column is not part of any user defined indexes.
* 3. the column is not used in any SQL statements inside of store
* procedures or triggers
* 4. the column is not part of any check-constraints
*
* The rules for dropping a column that was created as primary key:
*
* 1. the column is not defined as any foreign keys
* 2. the column is not defined as part of compound primary keys
*
* The rules for dropping a column that was created as foreign key:
*
* 1. the column is not defined as a compound foreign key. A
* compound foreign key is a foreign key consisted of more
* than one columns.

Note item (2) in the first part. But the engine makes a guess about an index being user-defined by comparing its prefix with "RDB$". Starting with v1.5,
underlying indices automatically inherit names from appropriate constraints provided that a constraint is explicitly named. In your first case, you've
added a named constraint and the index was named PK2_PK, so the engine has considered it being an user-defined one and hence prohibited the drop
operation. In the second case, there was no explicit constraint name given and the index name was generated using the RDB$-prefix, so the engine has
considered it being system-defined and allowed the column to be dropped.

In fact, this is a bug that was left unnoticed in v1.5. I thought I've got rid of the all explicitly RDB$-related checks, but apparently I missed this
particular case. It should be fixed.

Commits: 6fe9712