Sybase: how to update a column

By , last updated December 18, 2019

If you need to update a column in a table than you can use different approaches:

1) To modify a datatype of the column:

alter table Person modify COLUMN_A char(2) null

2) To make a column required:

First look if the column has some constraints:

sp_helpconstraint Person

If the column has constraints than you need to drop them first:

alter table Person drop constraint FK_PERSON_REF_ADRESS

Then if you can specify a default value for the column you can drop the column and create it again:

alter table Person drop COLUMN_A
alter table Person add COLUMN_A char(2) default 0 not null

But there is not the way we always need it. In such a case we need to drop the hole table and create it again:

drop table Person

create table Person(
   COLUMN_B     numeric(9,0)   null,
   COLUMN_C     char(2)        not null,
   COLUMN_D     date           not null,
   COLUMN_A     numeric(9,0)   identity,
   constraint PK_PERSON primary key (COLUMN_A),
   constraint FK_PERSON_REF_ADRESS foreign key (COLUMN_B)
         references Adress (COLUMN_ADRESS_A)
)