Unique Identifier Sql

Question

Following is a table of asp membership provider.I am trying to add multiple user of same application but I am unable to add duplicate values in ApplicationId column. Why is this so?

Here is my query which I am using to insert records in table:

Insert into Users 
values ('97C84F32-26A6-4A6C-960E-89A6A14D54E4', 'C2CADEDE-DA70-4C25-B717-6970344C8B34', 'John_doe', '', '', 1, '2014-11-05 21:28:33.043')

Insert into Users 
values ('97C84F32-26A6-4A6C-960E-89A6A14D54E4', 'D2CADEDE-CA70-4C25-B717-6970344C8B34', 'John_doe1', '', '', 1, '2014-11-05 21:28:33.043')

Error

Cannot insert duplicate key row in object 'dbo.Users' with unique index 'aspnet_Users_Index'. The duplicate key value is (97c84f32-26a6-4a6c-960e-89a6a14d54e4, ).

Here is my user table query :

CREATE TABLE [dbo].[Users]
(
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,

    PRIMARY KEY NONCLUSTERED ([UserId] 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

ALTER TABLE [dbo].[Users] ADD  DEFAULT (newid()) FOR [UserId]
GO

ALTER TABLE [dbo].[Users] ADD  DEFAULT (NULL) FOR [MobileAlias]
GO

ALTER TABLE [dbo].[Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]
GO

ALTER TABLE [dbo].[Users] WITH CHECK 
    ADD FOREIGN KEY([ApplicationId])
    REFERENCES [dbo].[Applications] ([ApplicationId])
GO

Any help would be really appreciated

Answer 1

The error you are getting is because you have a PRIMARY KEY on your table on the UserId column. This means you are unable to insert multiple rows with the same value in that column.

If you want to insert the same UserId but with different ApplicationId, you should change your primary key to be a composite key including both UserId and ApplicationId.

Alternatively, it may be that your INSERT statement needs changing as you are relying on column position being correct. Instead specify the columns:

Insert into Users
(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
values
('97C84F32-26A6-4A6C-960E-89A6A14D54E4','C2CADEDE-DA70-4C25-B717-6970344C8B34','John_doe','','',1,'2014-11-05 21:28:33.043')

Insert into Users
(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
values
('97C84F32-26A6-4A6C-960E-89A6A14D54E4','D2CADEDE-CA70-4C25-B717-6970344C8B34','John_doe1','','',1,'2014-11-05 21:28:33.043')

UPDATE

With a bit of research, the aspnet_Users table by default has a UNIQUE index on UserId and LoweredUserName. Just specify a unique LoweredUserName value:

Insert into Users
(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
values
('97C84F32-26A6-4A6C-960E-89A6A14D54E4','C2CADEDE-DA70-4C25-B717-6970344C8B34','John_doe','john doe','',1,'2014-11-05 21:28:33.043')

Insert into Users
(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
values
('97C84F32-26A6-4A6C-960E-89A6A14D54E4','D2CADEDE-CA70-4C25-B717-6970344C8B34','John_doe1','john_doe1','',1,'2014-11-05 21:28:33.043')