Stack Overflow
Stack Overflow

Questions tagged [alter-table]

The "ALTER TABLE" is a SQL statement that allows you to make schema changes to a database table (i.e. add, drop or alter columns from an existing table).

1118 questions
117
votes
19 answers

ALTER TABLE without locking the table?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a…
Daniel
  • 2,869
  • 4
  • 26
  • 28
115
votes
16 answers

add column to mysql table if it does not exist

My research and experiments haven't yielded an answer yet, so I am hoping for some help. I am modifying the install file of an application which in previous versions did not have a column which I want to add now. I do not want to add the column…
E Wierda
  • 1,295
  • 2
  • 9
  • 8
103
votes
5 answers

Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist

I have the following set up, CREATE TABLE auth_user ( id int PRIMARY KEY ); CREATE TABLE links_chatpicmessage (); I'm trying to add a column named sender to links_chatpicmessage which is a foreign key to another table called auth_user's id…
Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
99
votes
12 answers

MySQL: ALTER TABLE if column not exists

I have this code: ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 And I want to alter this table only if this column doesn't exist. I'm trying a lot of different ways, but nothing works: ALTER TABLE `settings` ADD…
BrunoRamalho
  • 1,726
  • 3
  • 17
  • 31
90
votes
9 answers

ALTER table - adding AUTOINCREMENT in MySQL

I created a table in MySQL with on column itemID. After creating the table, now I want to change this column to AUTOINCREMENT. How can this be done using ALTER statements? Table definition: ALLITEMS (itemid int(10) unsigned, itemname varchar(50)) I…
sumit
  • 10,935
  • 24
  • 65
  • 83
90
votes
5 answers

Change a Nullable column to NOT NULL with Default Value

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()). So far I've got the following…
Jim B
  • 8,344
  • 10
  • 49
  • 77
83
votes
5 answers

Add column to table and then update it inside transaction

I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back. I am having trouble…
Guillermo Gomez
  • 1,725
  • 4
  • 16
  • 21
81
votes
2 answers

How do I Alter Table Column datatype on more than 1 column?

For example: ALTER TABLE webstore.Store MODIFY COLUMN ( ShortName VARCHAR(100), UrlShort VARCHAR(100) ); The above however does not work. I am using MySql 5.x
JoJo
  • 4,643
  • 9
  • 42
  • 65
77
votes
3 answers

how to modify the size of a column

I created the table Test_Project2 in Oracle SQL Developer. After that I realized that the column proj_name is of a small size, so I decided to modify the column using the follwoing statement ALTER TABLE TEST_PROJECT2 MODIFY proj_name…
Amrmsmb
  • 1
  • 27
  • 104
  • 226
76
votes
4 answers

Right click script alter table disabled in SQL Server Management Studio

I want to script a table as Alter to a New Query Editor Window. But this option is disabled. How can I do this?
Himanshu
  • 31,810
  • 31
  • 111
  • 133
71
votes
5 answers

`MODIFY COLUMN` vs `CHANGE COLUMN`

I know, we cannot rename a column using MODIFY COLUMN syntax, but we can using CHANGE COLUMN syntax. My question is: what is the main usage of modify syntax? For example: ALATER TABLE tablename CHANGE col1 col1 INT(10) NOT NULL; instead of ALATER…
user2053420
70
votes
7 answers

MySQL very slow for alter table query

Why is it taking more than an hour to simply update this table to add a column? This table has 15M rows. It has 2 indexes and a single key primary key. The ALTER TABLE query has been in "copy to tmp table" state for 1 hour 15 minutes now. ALTER…
David Parks
  • 30,789
  • 47
  • 185
  • 328
69
votes
1 answer

Alter multiple columns in a single statement

I am using a query to alter the charset of a column ALTER TABLE `media_value_report` CHANGE `index_page_body` `index_page_body` TEXT CHARACTER SET utf8 NULL DEFAULT NULL i want to do this for other columns main_title, landing_page_body…
Sangram Anand
  • 10,526
  • 23
  • 70
  • 103
67
votes
5 answers

Modify a Column's Type in sqlite3

I'm pretty new to SQLite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;. Of course, I forgot to specify that column's type. The first thing I was thinking…
Jorge Israel Peña
  • 36,800
  • 16
  • 93
  • 123
55
votes
3 answers

ALTER TABLE on dependent column

I am trying to alter column datatype of a primary key to tinyint from int.This column is a foreign key in other tables.So,I get the following error: Msg 5074, Level 16, State 1, Line 1 The object 'PK_User_tbl' is dependent on column 'appId'. Msg…
Milee
  • 1,191
  • 1
  • 11
  • 29
1 2
3
…
74 75