Tag Info

New answers tagged

2

The first one is a unique constraint. It can be added to an existing table with: ALTER TABLE ADD CONSTRAINT ... Details in the manual here. It is implemented using a unique index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. A uniqueness ...


0

Certain statements trigger an implicit commit. ALTER TABLE is definitely one of them. See my post row locking within ACID transaction innodb on all statements that do this. With regard to your transaction, any of the updates that fail will rollback previous updates. You need to create a flag that checks whether or not the transaction went to completion. ...


2

In MySQL most DDL statements, including ALTER TABLE, implicitly issue a COMMIT before executing. It's described in the manual. One way to do what you want is reorder your statements, placing updates after the alter.


0

I have discussed situations like this before in these posts: May 10, 2013 : In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table Sep 22, 2013 : How to create root user after running mysql_install_db? In your case do this STEP #1 Add these lines just under the [mysqld] group header in my.ini [mysqld] skip-grant-tables ...


0

Is there any other (faster) way to increase column size? You can try the parallel clause alter table shipment modify (description varchar2(21) ) parallel 8; And you can try the dbms_redefinition package. Redefining Tables Online


1

If you want even more automation, then download SQLcl, then you can do fun things like ctas current_table new_table and it generates necessary code for you. e.g., SQL> ctas my_table new_table CREATE TABLE OWNER."NEW_TABLE" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", ...


1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...



Top 50 recent answers are included