I think you will need to do this as three separate statements. I’ve been looking around and everything i’ve seen seems to suggest you can do it if you are adding a column, but not if you are altering one. ALTER TABLE dbo.MyTable ADD CONSTRAINT my_Con DEFAULT GETDATE() for created UPDATE MyTable SET Created = … Read more
CREATE TABLE ALLITEMS( itemid INT(10)UNSIGNED, itemname VARCHAR(50) ); ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY; DESC ALLITEMS; INSERT INTO ALLITEMS(itemname) VALUES (‘Apple’), (‘Orange’), (‘Banana’); SELECT * FROM ALLITEMS; I was confused with CHANGE and MODIFY keywords before too: ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY; ALTER TABLE ALLITEMS MODIFY itemid INT(5); … Read more
Use the following in a stored procedure: IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”tablename” AND table_schema=”db_name” AND column_name=”columnname”) THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default ‘0’; END IF;
To add a constraint to a column It needs to exists first into the table there is no command in Postgresql that you can use that will add the column and add the constraint at the same time. It must be two separate commands. You can do it using following commands: First do as: ALTER … Read more
I have a 99% pure SQL method. The idea is to version your schema. You can do this in two ways: Use the ‘user_version’ pragma command (PRAGMA user_version) to store an incremental number for your database schema version. Store your version number in your own defined table. In this way, when the software is started, … Read more
Here is a working solution (just tried out with MySQL 5.0 on Solaris): DELIMITER $$ DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$ CREATE PROCEDURE upgrade_database_1_0_to_2_0() BEGIN — rename a table safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=’my_old_table_name’) ) THEN RENAME TABLE my_old_table_name TO my_new_table_name, END IF; — add a column safely … Read more
The only other option is to do manually what many RDBMS systems do anyway… – Create a new table You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would … Read more
try: ALTER TABLE `user` CHANGE `id` `id` INT( 11 ) COMMENT ‘id of user’
[2017] Update: MySQL 5.6 has support for online index updates https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the … Read more
As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint: ALTER TABLE MY_TABLE ADD STAGE INT NULL GO UPDATE MY_TABLE SET <a valid not null values for your column> GO ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT … Read more