Board index » delphi » Drop a field in a primary key

2006-01-25 11:26:51 PM
delphi100
Hi!
I just discovered something.
I mistakely dropped a field which was in the primary key. The result:
The PK and the field is dropped.
I use Interbase v7.5.1.80.
The error can be reproduced:
Create table HELLO(
ID Integer Not Null,
Texte varchar(20),
primary key(ID));
then
alter table hi drop id;
So... My PK is gone, so is the field. IB should raise an exception when
trying to drop a PK field.
Is this a bug or a normal behaviour of IB?
Thanks
VB
 

Hello Vincent,
Especially because it is not consistent. For example, take this:
CREATE TABLE HELLO
(
TEXTE VARCHAR( 20) COLLATE NONE,
ID INTEGER
)
;
CREATE ASC INDEX I_HELLO_ID ON hi (ID);
commit;
Now, dropping ID causes this error to be raised:
ISC ERROR CODE:335544351
ISC ERROR MESSAGE:
unsuccessful metadata update
column ID from table hi is referenced in index I_HELLO_ID
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

What behavior would you like? Do you think that IB should require that
you drop every constraint that applies to a column before you can drop
the column?
In your example there are no dependencies on the primary key. For
example, if you have a foreign key constraint that references the
primary key I believe you will get an error if you try to drop the
column.
My guess is that the reasoning is that there is no need for an error if
there are no objects external to the table that are dependent on the
column. Martijn's point makes sense if you consider the index that is
created to enforce the primary key constraint as an external object.
However, another view is that the index in Martijn's example is a valid
dependency since it was explicitly created. The index created by the
primary key contraint is not a separate object at the logical level. It
is just a physical implementation detail.
I leave it to someone else to tell us which is the "right" view.
--
Bill Todd (TeamB)
 

can have the column without the constraint (this is different from, for
example, a "not null" constraint, which is implicit with the column).
The behaviour is also different from dropping a column in a compound
primary key ->then InterBase will raise an error.
"unsuccessful metadata update ERASE RDB$RELATION_FIELDS
failed action cancelled by trigger (1) to preserve data integrity Cannot
delete column being used in an Integrity Constraint. "
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Martijn Tonies writes:
that makes a difference.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: learningtowhistle.blogspot.com
 

Hi Bill.
is and as a stored proc is. In the same way that an index should be dropped.
I was very surprise to see that.
VB