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
257
votes
3 answers

How to alter a column's data type in a PostgreSQL table?

Entering the following command into a PostgreSQL interactive terminal results in an error: ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11); What is the correct command to alter the data type of a column?
tree em
  • 20,379
  • 30
  • 92
  • 130
238
votes
21 answers

Alter a MySQL column to be AUTO_INCREMENT

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification. ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am I doing something wrong…
C. Ross
  • 31,137
  • 42
  • 147
  • 238
226
votes
6 answers

ALTER TABLE to add a composite primary key

I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination. How would I ALTER TABLE to…
David542
  • 104,438
  • 178
  • 489
  • 842
222
votes
9 answers

Postgresql - change the size of a varchar column to lower length

I have a question about the ALTER TABLE command on a really large table (almost 30 millions rows). One of its columns is a varchar(255) and I would like to resize it to a varchar(40). Basically, I would like to change my column by running the…
Labynocle
  • 4,062
  • 7
  • 22
  • 24
222
votes
8 answers

How do I add more members to my ENUM-type column in MySQL?

The MySQL reference manual does not provide a clearcut example on how to do this. I have an ENUM-type column of country names that I need to add more countries to. What is the correct MySQL syntax to achieve this? Here's my attempt: ALTER TABLE…
Zaid
  • 36,680
  • 16
  • 86
  • 155
210
votes
5 answers

Can I create a named default constraint in an add column statement in SQL Server?

In SQL Server, I have a new column on a table: ALTER TABLE t_tableName ADD newColumn NOT NULL This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint. To get around this, I…
GlennS
  • 5,251
  • 6
  • 28
  • 31
209
votes
7 answers

Alter column, add default constraint

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect syntax near…
ram
  • 11,468
  • 16
  • 63
  • 89
203
votes
3 answers

How to add not null constraint to existing column in MySQL

I have table name called "Person" with following column names P_Id(int), LastName(varchar), FirstName (varchar). I forgot to give NOT NULL Constraint to P_Id. Now I tried with following query to add NOT NULL Constraint to existing column called…
mymotherland
  • 7,968
  • 14
  • 65
  • 122
199
votes
11 answers

How do I alter the position of a column in a PostgreSQL database table?

I've tried the following, but I was unsuccessful: ALTER TABLE person ALTER COLUMN dob POSITION 37;
Huuuze
  • 15,528
  • 25
  • 72
  • 91
195
votes
4 answers

sqlite alter table add MULTIPLE columns in a single statement

Is it possible to alter table add MULTIPLE columns in a single statement in sqlite? The following would not work. alter table test add column mycolumn1 text, add column mycolumn2 text;
user775187
  • 22,311
  • 8
  • 28
  • 36
178
votes
8 answers

insert a NOT NULL column to an existing table

I have tried: ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL; But it gives this error message: ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified
ANP
  • 15,287
  • 22
  • 58
  • 79
172
votes
3 answers

How to change MySQL column definition?

I have a mySQL table called test: create table test( locationExpect varchar(120) NOT NULL; ); I want to change the locationExpect column to: create table test( locationExpect varchar(120); ); How can it be done quickly?
Mask
  • 33,129
  • 48
  • 101
  • 125
147
votes
4 answers

Create an index on a huge MySQL production table without table locking

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement... Is there a way to create that index without blocking inserts and selects? Just wondering I…
n0cturnal
  • 1,615
  • 2
  • 11
  • 8
136
votes
5 answers

Alter MySQL table to add comments on columns

I have been checking the MySQL Documentation for ALTER TABLE and it does not seem to include a way to add or modify a comment to a column. How can I do this? -- for table ALTER TABLE myTable COMMENT 'Hello World' -- for columns -- ???
Jhonny D. Cano -Leftware-
  • 17,663
  • 14
  • 81
  • 103
119
votes
16 answers

ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it alone. Unfortunately, SQLite doesn't…
dan04
  • 87,747
  • 23
  • 163
  • 198
1
2
3
…
74 75