How to write query for remove and alter unique columns

Question

My scenario..Im using SqlCompactServer Edition .I'm creating table Create Table SSr( name nvarchar(400),id int unique).I need to alter table and make name as UNIQUE Column and remove unique from existing column. How to Achieve this.

Answer 1

Use sp_help to idenfity the old unique or primary key constraint name. Then drop it by name, before adding your new unique constraint.

EXEC sp_help 'SSr'
ALTER TABLE [SSr] DROP CONSTRAINT [UQ_SSr_Id]
ALTER TABLE [SSr] ADD CONSTRAINT [UQ_SSr_Name] UNIQUE ([Name])

You can also query the information schema to identify primary key and unique constraints:

select CONSTRAINT_NAME, CONSTRAINT_TYPE
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
and TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'SSr'