How to add unique column in sybase?

I have a sybase db table in which i need to add a n开发者_如何转开发ew column.

The conditions: The column must not allow nulls and be unique. what is the alter table sql to achieve this?

EDIT: It is a varchar type column.Yes the table as of now is empty, but when filled it is ensured that unique values would be filled in.

I tired executing

alter table Test add Name varchar not null unique

i get error saying default value must be specified as not null is given. but i want to add unique constraint so do i really need to specify default?

thanks

---

Unique values are specified as part of an index on the column, not in the column definition itself.

Try:

alter table Test add Name varchar not null
create unique index index_name_unique on Test (Name)

The ASE reference manual can help with more detail.

---

Once a table has been created Sybase ASE does not allow addition of NOT NULL column directly unless a default is specified for the column. However, if the table is still empty you can do the following -

1. First add the new column as a NULL column to the table using alter table command -

alter table Test add Name varchar(100) null

1. Once this has been done, try modifying the same column Name in the table Test using the alter table script -

alter table Test modify Name varchar(100) NOT NULL

and you will see that you are able to modify the Name column to a NOT NULL column using these steps. This is because at this time Sybase server checks as there is no data in the table hence the NOT NULL constraint is not checked and the column is made NOT NULL. Hence, we are able to skip the default constraint.

In case there would have been some data already present in the table Test, then we need to add one more step in between steps 1 and 2 which will add default values to the existing rows in the table. This can be done via a script for previous data and then following the step 2.

To make the column only allow unique values for the column you need to add a unique key constraint using the following syntax -

alter table Test add constraint UK1 unique(Name)