how do I alter table to remove unique field

SET QUOTED_IDENTIFIER ON
GO
I have the following table.  How do I alter the table to remove unique from field ViewName by iteself.

IDRoleApps and ViewName together is fine.


CREATE TABLE [dbo].[ViewName](
      [IDViewName] [bigint] IDENTITY(1,1) NOT NULL,
      [ViewName] [nvarchar](50) NOT NULL,
      [ViewType] [nvarchar](50) NOT NULL,
      [ViewDescription] [nvarchar](255) NOT NULL,
      [IDRolesApps] [bigint] NULL,
 CONSTRAINT [ViewName_PrimaryKey] PRIMARY KEY CLUSTERED
(
      [IDViewName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IDViewName] UNIQUE NONCLUSTERED
(
      [ViewName] ASC,
      [IDRolesApps] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
      [ViewName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
goodkAsked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
OK, use the management studio to see the index as described above, then use the name in the statement supplied above.


Kelvin
0
 
Kelvin SparksCommented:
It appears to be added as a Unique Index and without a name supplied by the code. I suspect it will have a system generated name which you will have to retrieve and  then use to drop the Indie (or possibly a constraint).


Kelvin
0
 
goodkAuthor Commented:
Hi Kelvin,

How do i find the unique index? and then how do i use it to alter the table to remove unique index from Viewname field?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
goodkAuthor Commented:
Hi Kelvin,

How do i find the system generated name? and then how do i use it to alter the table to remove unique index from Viewname field?
0
 
Kelvin SparksCommented:
Do you have access to the database itself via SSMS?
0
 
goodkAuthor Commented:
I am using MS sql server management studio and I can run queries on the sql db.
0
 
Kelvin SparksCommented:
OK, open the table in design, right click in and empty part of the designer and choose Indexes.

Review those list - lick each in turn on the left and see which fields are included in the right hand panel. The names on the left are the Index (or constraint names). You can delete it there by selecting it and deleting or using the syntax

ALTER TABLE tablename DROP CONSTRAINT indexname

Kelvin
0
 
goodkAuthor Commented:
It is a shared db so can see the design but cannot interact or modify it.

Alter table commands would work.
0
 
goodkAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.