ALTER TABLE to add a composite primary key
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing); If a primary key already exists then you want to do this ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
alter-table
How do I alter the position of a column in a PostgreSQL database table?
“Alter column position” in the PostgreSQL Wiki says: PostgreSQL currently defines column order based on the attnum column of the pg_attribute table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout. That’s pretty weak, but in their defense, … Read more
ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
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
Error renaming a column in MySQL
Lone Ranger is very close… in fact, you also need to specify the datatype of the renamed column. For example: ALTER TABLE `xyz` CHANGE `manufacurerid` `manufacturerid` INT; Remember : Replace INT with whatever your column data type is (REQUIRED) Tilde/ Backtick (`) is optional
MySQL: ALTER TABLE if column not exists
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;
How can I modify the size of column in a MySQL table?
Have you tried this? ALTER TABLE <table_name> MODIFY <col_name> VARCHAR(65353); This will change the col_name‘s type to VARCHAR(65353)
Adding multiple columns AFTER a specific column in MySQL
Try this ALTER TABLE users ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`, ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`, ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`; check the syntax
Rename column SQL Server 2008
Use sp_rename EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’ See: SQL SERVER – How to Rename a Column Name or Table Name Documentation: sp_rename (Transact-SQL) For your case it would be: EXEC sp_RENAME ‘table_name.old_name’, ‘new_name’, ‘COLUMN’ Remember to use single quotes to enclose your values.
Altering a column: null to not null
First, make all current NULL values disappear: UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL Then, update the table definition to disallow “NULLs”: ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL
Alter a MySQL column to be AUTO_INCREMENT
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment