Stack Overflow
Stack Overflow

Questions tagged [alter]

'alter' is a SQL keyword used to change or modify the table structure or the records in a table.

835 questions
20
votes
3 answers

Make a column nullable in DB2 when Data Capture is enabled

I'm using db2 version 9.7* and it seems impossible to make a NOT NULL column nullable in any straightforward way. Unfortunately the solution of using a more developer friendly database is not available. Basically, in MySQL speak, I want to do…
lukewm
  • 21,433
  • 6
  • 26
  • 28
19
votes
4 answers

Alter column length in Schema builder?

I have two fields i need to increment the character limit on. I're read through the documentation and to my surprise i found no option for it. Is it possible to do? If not, how should i go about solving this? I could drop the column and re-create it…
qwerty
  • 5,166
  • 17
  • 56
  • 77
18
votes
3 answers

How can I alter multiple tables at once in mysql?

I am trying to alter multiple tables and change the size of the username VARCHAR column to 999 as its current size is too small and now things are screwed up. How can I do this? I have tried the following and it worked for one table but when trying…
John Doe
  • 3,559
  • 15
  • 62
  • 111
18
votes
4 answers

Removing NOT NULL restriction from column in MySQL

How can I alter a column whose restricted to NOT NULL to accept NULL values?
Aufwind
  • 25,310
  • 38
  • 109
  • 154
14
votes
4 answers

mysql alter int column to bigint with foreign keys

I want to change the datatype of some primary-key columns in my database from INT to BIGINT. The following definition is a toy-example to illustrate the problem: CREATE TABLE IF NOT EXISTS `owner` ( `id` int(11) NOT NULL AUTO_INCREMENT, …
deif
  • 1,349
  • 1
  • 14
  • 19
14
votes
4 answers

Add index to table if it does not exist

I want to add an index to a table by using the ALTER syntax, but first check if it already exists on the table, and only add the index if it does not exist. ALTER TABLE tableName ADD INDEX IX_Table_XYZ (column1); Is there any way to do this?
Manish Kumar
  • 595
  • 2
  • 5
  • 20
13
votes
2 answers

How to alter MySQL table without losing data?

In my application, I make some changes and upload them to a testing server. Because I have no access to the server database I run ALTER commands to make changes on it. Using a method I ran the following command on server: ALTER TABLE `blahblahtable`…
user605334
13
votes
1 answer

Difficulty in upgrading SQlite table

I have an application running with a working table called ANIMAL. Upon first creating this table it consisted simply of _id and animal_name columns. Now I am trying to expand on it, including a animal_biography column, however I am having a little…
Php Pete
  • 762
  • 3
  • 14
  • 29
13
votes
1 answer

ALTERing a sqlite table to add a timestamp column with default value

It isn't possible to ALTER table ADD column foo timestamp default CURRENT_TIMESTAMP in sqlite, but are there any clever workarounds?
user3791372
  • 4,445
  • 6
  • 44
  • 78
13
votes
4 answers

mysql add "prefix" to every value in column

I need to add a 'prefix' in front of every value in a certain column. Example: all fields in column x are: 200, 201, 202, 203, etc. I need them to be pn_200, pn_201, pn_202, pn_203, etc. Is there a way to use ALTER or MODIFY commands to do this? I…
Ghost Echo
  • 1,997
  • 4
  • 31
  • 46
13
votes
4 answers

How to alter a MySQL table's foreign key using the command line

How to alter an existing table in MySQL, setting foreign key to another table, using the command line?
el ninho
  • 4,183
  • 15
  • 56
  • 77
12
votes
3 answers

How to modify datatype of a column with a default value

I'm trying to change the datatype of a column in SQL Server from tinyint to smallint. But there's a default value on my column and I don't know the name of the constraint. Is there an easy way to do it ? This don't work because of the default…
GregM
  • 2,634
  • 3
  • 22
  • 37
12
votes
4 answers

remove ON DELETE CASCADE

I have a child table. and foreign key there with ON DELETE CASCADE while creating the table. There are no records either in child or parent table. I want the primary key, foreign key to be as they are but want to remove only the CASCADING option…
Raghav
  • 195
  • 1
  • 3
  • 6
11
votes
4 answers

mysql: how to truncate the length of a field

Alter table merry_parents change mobile mobile char(10). When I do the above I'm getting error: #1265 - Data truncated for column 'mobile' at row 2 How can I truncate my mobile field to char(10)? Currently it is char(12).
vaanipala
  • 1,261
  • 7
  • 36
  • 63
11
votes
4 answers

alter table mysql offline or not?

I need to add a column to my current table. This table is used a lot during the day and night. i found out i need to alter using the alter command found here http://dev.mysql.com/doc/refman/5.1/en/alter-table.html I tested it on a development…
Lauren Smith
  • 508
  • 3
  • 12
1 2
3
…
55 56