Issue to query

Question

Hi,
What is reason of error like

Msg 8151, Level 16, State 0, Line 49
Both a PRIMARY KEY and UNIQUE constraint have been defined for column 'compaddr', table 'dbo.user_main'. Only one is allowed.

on this query?

CREATE TABLE [dbo].user_main ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[user_main] ADD [photo_file_fullname] varchar NULL
ALTER TABLE [dbo].[user_main] ADD [photo_file_name] varchar NULL
ALTER TABLE [dbo].[user_main] ADD [photo_file_ext] varchar NULL
ALTER TABLE [dbo].[user_main] ADD [addr1] varchar NULL
ALTER TABLE [dbo].[user_main] ADD [addr2] varchar NULL
ALTER TABLE [dbo].[user_main] ADD [company_id] [int] NULL
ALTER TABLE [dbo].[user_main] ADD [company] nvarchar NULL
ALTER TABLE [dbo].[user_main] ADD [notify_flag] [bit] NULL
ALTER TABLE [dbo].[user_main] ADD [usertype] nvarchar NOT NULL
ALTER TABLE [dbo].[user_main] ADD [compname] nvarchar NULL
ALTER TABLE [dbo].[user_main] ADD [compaddr] nvarchar NULL
PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CONSTRAINT [cons_user_main2] UNIQUE NONCLUSTERED
(
[user_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[user_main] ADD CONSTRAINT [df_approvedbyAdmin] DEFAULT ((1)) FOR [approvedbyAdmin]
GO

ALTER TABLE [dbo].[user_main] ADD DEFAULT (' ') FOR [usertype]
GO

ALTER TABLE [dbo].[user_main] ADD DEFAULT (' ') FOR [compname]
GO

ALTER TABLE [dbo].[user_main] ADD DEFAULT (' ') FOR [compaddr]
GO

Answer 1

You can do this, but you must create the UNIQUE constraint in a separate statement. So the end of your CREATE TABLE statement would be
ALTER TABLE [dbo].[user_main] ADD [compname] nvarchar NULL
ALTER TABLE [dbo].[user_main] ADD [compaddr] nvarchar NULL
PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

and then you would follow that with

ALTER TABLE [dbo].[user_main] ADD CONSTRAINT [cons_user_main2] UNIQUE NONCLUSTERED
(
[user_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tom