Add a NOT NULL column to table using Alter Table command in Sybase ASE

While working with database development using Sybase ASE every now and then we tend to hit on a problem due to change in requirements to add new columns to the table definition.
Alter Table command is one such command that helps us add/remove/modify columns from the table efinition. Now you must be thinking that this sounds pretty easy so where is the catch? Well, the biggest catch while using alter table command is that the alter table command does not allow addition of a NOT NULL column to a Sybase table directly.

Wondering why I imphasized on the use of word directly? Well, because just try to use this simple command to add a NOT NULL column to the table -

alter table <<table name>>
add <<column name>> <<data type>>  NOT NULL

and you will be greeted with a nice error message suggesting that this is not allowed in Sybase ASE without specifying a default value for this column. Now, the reason behind this error message is that once the table has been created and a number of rows exist in this table then the addition of a new column should provide some default values for the rows already existing in the table as we want this column to be a NOT NULL column.

But, sometimes we may require that we add a new NOT NULL column to our Sybase ASE table without the use of default. Why? Because we dont want that once this new field has been added to our table then any developer or a user application can insert the data in the table without explicitly specifying some value for this column either purposely our by mistake.

So, now that our requirement has been justified and the error message hitting us what should we do to add a NOT NULL column to our existing table without specifying the default for this column?

Well, enough of the problem definition its time to figure out a solution. So here it is-

To demonstrate the solution lets create a simple Employee table with the some columns. The create table definition for the same is provided below -

create table Employee

(

  EmpId int NOT NULL,

  FirstName varchar(100) NULL,

  LastName varchar(100) NULL,

  Age int  NULL,

  Salary float  NULL

)

go

Now, after creating this table we find that the requirement has changed and we need to add a NOT NULL DeptId (Department Id) column to this table as we can't have an Employee without a Department in our database design. So, now we first try (we try because our Sybase server might get into a bad mood if we try to go straight against his will, you see we can't make our server go bad at us) to convince our Sybase server to allow us add the NULL DeptId column to the table Employee. So, the syntax for column addition is -

alter table Employee

add DeptId int NULL

go

Now, this being done we will humbly try to place one more request to our Sybase ASE server to allow us modify the newly added DeptId column to NOT NULL; and the statment is -

alter table Employee

modify DeptId int NOT NULL

go

and... Great we did it.... :) .. Now, we see that this time Sybase ASE Server allows us to modify the DeptId column to a NOT NULL column without showing the same error message that we previously encountered. To confirm the same we can also check the status column in syscolumns table for our Employee table to see that the Sybase server has corrected our entry for DeptId column to NOT NULL. The status field in syscolumns should now show 0 for DeptId column as DeptId column is now a NOT NULL column.

Well, thats all for now will get back with some more stuffs later. But before I leave I would like to mention that this approach works for Sybase ASE 12.5.x and later. It might work for earlier versions but the same has not been tested by me so you can try your luck for previous versions.

Have a nice day.