New answers tagged

0

I had the same problem with Maria DB 10.1.12, then after reading the documentation I found that there is an option to perform the operation "in-place" which eliminates the copy of the table. With this option the alter table is very fast. In my case it was: alter table user add column (resettoken varchar(256), resettoken_date date, resettoken_count int), ...


0

FYI... Changelog 2014-03-27 5.6.17 -- Functionality Added or Changed -- The IGNORE clause for ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and ...


0

I wrote a stored procedure to create indexes and check if one already exists by name. The code for it is in my answer to MySQL: Create index If not exists I wrote Sep 19, 2012 I tested that code in that old post. It works. The Stored Procedure checks for the index name only, not the column list combination (See the bottom of this post for what to do if ...


4

Realizing that this is a complete redesign of your intent but why not script out the change for each table? Ie create a new table, copy the data over, re-create all indexes, security etc. The easiest way to do this would be to use the designer, make your changes, don't hit save, but instead right click on the background of the designer and request "...


2

A default value is not a constraint in Oracle. You simply alter the column as: SQL> create table tab1 (col1 number, col2 number); Table created. SQL> alter table tab1 modify (col2 default 1); Table altered. SQL> select * from user_constraints; no rows selected SQL> insert into tab1 (col1) values (0); 1 row created. SQL> select * from ...


0

BLOB, TEXT, GEOMETRY, and JSON columns cannot be assigned a default value. http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html What you are in essence asking -- without realizing it, presumably -- is how to force MySQL to revert to its old, incorrect behavior of ignoring your incorrect table definition and pretending that you really said ...


3

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine): Storage, Performance, and Concurrency Considerations In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that ...



Top 50 recent answers are included