Stack Overflow
Stack Overflow

Questions tagged [alter-column]

Alter column is a part of sql's Alter table statement used to change an existing column in a table.

Alter column can be used to set a default value, changing the column's data type, or changing the column's maximum length.

Alter column is supported in Microsoft Access and in Sql Server. To modify columns properties in Oracle, for example, you will need to use Modify column instead.

Alter column related tags

52 questions
1272
votes
14 answers

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved. I am looking…
Karmic Coder
  • 16,622
  • 5
  • 29
  • 41
195
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,033
  • 16
  • 59
  • 86
164
votes
13 answers

How to ALTER multiple columns at once in SQL Server

I need to ALTER the data types of several columns in a table. For a single column, the following works fine: ALTER TABLE tblcommodityOHLC ALTER COLUMN CC_CommodityContractID NUMERIC(18,0) But how do I alter multiple columns in one statement?…
D.mahesh
  • 1,649
  • 2
  • 10
  • 3
135
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?
kn3l
  • 16,471
  • 26
  • 80
  • 116
88
votes
5 answers

When increasing the size of VARCHAR column on a large table could there be any problems?

I'm using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows. What I need to know is if there are any issues I have not considered. I will be using this TSQL statement: ALTER TABLE…
Paul T Davies
  • 2,377
  • 1
  • 18
  • 34
77
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
  • 7,736
  • 10
  • 46
  • 75
50
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,121
  • 1
  • 10
  • 27
15
votes
3 answers

Change a column to not allow nulls

So I want to change a column in my SQL Server database to not allow nulls, but I keep getting an error. this is the sql statement I am using: alter table [dbo].[mydatabase] alter column WeekInt int not null and this is the error I am getting : Msg…
user2255811
  • 456
  • 1
  • 8
  • 19
13
votes
3 answers

SQL: ALTER COLUMN to shorter CHAR(n) type

I'm working with MS SQL SERVER 2003. I want to change a column in one of my tables to have fewer characters in the entries. This is identical to this question: Altering a Table Column to Accept More Characters except for the fact that I want fewer…
Vivian River
  • 28,530
  • 54
  • 179
  • 298
9
votes
1 answer

How do I change a column from null to not null when an index depends on that column without recreating the index?

I have a column Column which is declared to be NULL DEFAULT(GETUTCDATE()) and there's a non-clustered index that includes this column. I want to change this column to be NOT NULL DEFAULT(GETUTCDATE()) and when I run ALTER TABLE ALTER COLUMN…
sharptooth
  • 159,303
  • 82
  • 478
  • 911
8
votes
2 answers

SQL SET DEFAULT not working in MS Access

Possible Duplicate: DEFAULT clause in ALTER TABLE statement resulting in syntax error I am trying to execute the following statement using a SQL query within MS Access; ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT 'default…
Steztric
  • 2,532
  • 1
  • 22
  • 36
3
votes
2 answers

MSSQL: alter column not null, in general

In Microsoft SQL Server 2008 R2, I would like to change a nullable column to not null. Obviously, I could do this by restating the datatype, as alter table t alter column c int not null if the column t.c is of int datatype, for example. But what…
Ed Avis
  • 1,220
  • 14
  • 35
3
votes
1 answer

Convert postgresql column from character varying to integer

I'm trying to change a column type from "character varying(15)" to an integer. If I run "=#SELECT columnX from tableY limit(10);" I get back: columnX ---------- 34.00 12.00 7.75 18.50 4.00 11.25 18.00 16.50 If i run "=#\d+ columnX" i get back: …
bk201
  • 327
  • 2
  • 5
  • 13
2
votes
2 answers

Behind the scene operations for ALTER COLUMN statement in SQL Server

I am altering the column datatype for a table with around 100 Million records using the below query: ALTER TABLE dbo.TARGETTABLE ALTER COLUMN XXX_DATE DATE The column values are in the right date format as I inserted original date from a valid data…
George Xu
  • 59
  • 3
2
votes
3 answers

Change Auto-Increment, Primary Key field from SMALLINT to INT in SQL Server

What is the best way (low impact/low risk) to change a primary key field from SMALLINT to INT? The field is configured to use "Identity Increment" to auto-increment. I'm starting with the following SQL: ALTER TABLE category_types ALTER COLUMN id…
user2578229
  • 135
  • 2
  • 7
1
2 3 4