Thread: wrong order of drop ... create ... sentences
Hi:
I just realized that I am having the following trouble :
If open properties of a table, to adding a column i.e., and that table
has some constraints, the SQL tab show code similar to :
ALTER TABLE circulation.deliveries ADD CONSTRAINT
deliveries_delivery_type_id_fk FOREIGN KEY (delivery_type_id)
REFERENCES circulation.deliveries_types (delivery_type_id) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE circulation.deliveries DROP CONSTRAINT
deliveries_delivery_type_id_fk;
(there are other constraints, deleted for easing the lecture)
which cause an error because constraint already exist: DROP is after
ADD. And these code should not appears at all, because I did not any
change to these constraints. Usually says "--nothing to change" or
similar.
It is a bug or I am doing anything wrong ?. I guess I am using revision
4442.
Regards,
Diego.
Hi, It seems strange to me that nobody else reported this behavior, maybe a bug. It just me or what ?. :-). Today I compiled revision 4455, with an updated postgresql 8.1 beta 2 and still can't modify any table due to "ADD before DROP" issue. It seems to happen only in table properties dialog. I can add or modify any table object using ad-hoc property dialogs. To reproduce it, open a table property dialog and look to SQL tab. It should say "-- nothing to change" but in my case shows constraints's add code and corresponding constraints's drop code. Regards, Diego. El vie, 16-09-2005 a las 23:53 -0300, Diego Gil escribió: > Hi: > > I just realized that I am having the following trouble : > > If open properties of a table, to adding a column i.e., and that table > has some constraints, the SQL tab show code similar to : > > ALTER TABLE circulation.deliveries ADD CONSTRAINT > deliveries_delivery_type_id_fk FOREIGN KEY (delivery_type_id) > REFERENCES circulation.deliveries_types (delivery_type_id) MATCH > SIMPLE > ON UPDATE RESTRICT ON DELETE RESTRICT; > > ALTER TABLE circulation.deliveries DROP CONSTRAINT > deliveries_delivery_type_id_fk; > > (there are other constraints, deleted for easing the lecture) > > which cause an error because constraint already exist: DROP is after > ADD. And these code should not appears at all, because I did not any > change to these constraints. Usually says "--nothing to change" or > similar. > > It is a bug or I am doing anything wrong ?. I guess I am using revision > 4442. > > Regards, > Diego. > > >
hi! thank you for fixing the sql query order in svn. i have, however, noticed some other things which might be annoying: when wanting to change a table name and a column name at the same time, pgadmin produces this (this is all done via the properties option from the context menu): ALTER Table budgettype RENAME TO budget_type; ALTER TABLE budgettype RENAME budgettype_name TO budget_type_name; which will fail because the table name ought to get changed with the first query. pgadmin keeps working with the original table name instead of using the new table name after issuing the rename table statement. and, not wanting to nag, but to be consistent, the keyword "Table" ought to be uppercase, like everything else ;) also, i wanted to remove a constraint (primary key) and add a new one and pgadmin produced thie following code: CONSTRAINT ALTER TABLE budget_type DROP CONSTRAINT pkey_budgettype; ALTER TABLE budget_type ADDpkey_budget_type PRIMARY KEY (id); there is an unneccesary " CONSTRAINT " in front of the first query and in the second query there is no space between the ADD keyword and constraint name. i am using pgadmin3 compiled from svn (fresh checkout from an hour ago), on suse linux 9.3. cheers, Miha
hi again!
Miha Radej wrote:
> also, i wanted to remove a constraint (primary key) and add a new one
> and pgadmin produced thie following code:
>
> CONSTRAINT ALTER TABLE budget_type DROP CONSTRAINT pkey_budgettype;
> ALTER TABLE budget_type ADDpkey_budget_type PRIMARY KEY (id);
>
>
> there is an unneccesary " CONSTRAINT " in front of the first query and
> in the second query there is no space between the ADD keyword and
> constraint name.
i tried fiddling with the code (never touched c++ before so i was a bit
afraid i'd mess things up) and a small change fixed this specific issue,
here is the svn diff:
Index: src/dlg/dlgTable.cpp
===================================================================
--- src/dlg/dlgTable.cpp (revision 4459)
+++ src/dlg/dlgTable.cpp (working copy)
@@ -377,7 +377,7 @@
tmpsql += wxT("ALTER TABLE ") + tabname
+ wxT(" ADD");
if (!conname.IsEmpty())
- sql += wxT(" CONSTRAINT ");
+ tmpsql += wxT(" CONSTRAINT ");
tmpsql += definition + wxT(";\n");
}
cheers,
Miha