Sybase Alter Table is used to modify the table structure.
With Sybase Alter Table:
- We can add new columns to a table
- Drops or modify existing columns;
- Add, change, or drop constraints;
- Changes properties of an existing table;
- Enable or disable triggers on a table,
Change the compression level of a table.
Support adding, dropping, and modifying computed columns, and enable the materialized property, nullability, or definition of an existing computed column to be changed.
Sybase Alter Table Syntax:
alter table [[database.][owner].table_name paratemeters_to_be_changed
Permissions required to alter table is ALTER TABLE privilege.
To alter table, we have to set “select into/bulkcopy/pllsort” true. Otherwise it will through error.
sp_dboption database_name, "select into/bulkcopy/pllsort", true
Examples:
1. Create table titles
create table titles ( title_id int not null , title varchar (80) not null , type char (12) not null , pub_id char (4) null , price money null , advance money null , total_sales int null , notes varchar (200) null , pubdate datetime not null , contract bit not null )
2. Add a column with data type as varchar to the table titles.
alter table titles add language varchar (40) null
3. Add primary key constraint to the titles table.
alter table titles add constraint tt_publication primary key (title_id)
4. Make column to not null
alter table titles modify pub_id not null
5. Drop a column from table.
alter table titles drop language