Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.

Hi.

I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1

But, now I am getting the below error:

Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.

Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.

Any suggestions as to where I messed up and how to deal with this?

Thanks.


DBO.USERS






Code Snippet

CREATE TABLE [dbo].[users](
    [userid] [int] IDENTITY(1,1) NOT NULL,
    [lastname] [varchar](50) NULL,
    [firstname] [varchar](50) NULL,
    [email] [varchar](50) NOT NULL,
    [alternateemail] [varchar](50) NULL,
    [password] [varchar](50) NOT NULL,
    [role] [varchar](10) NOT NULL,
    [securityquestion] [varchar](50) NOT NULL,
    [securityanswer] [varchar](50) NOT NULL,
    [zipcode] [int] NOT NULL,
    [birthmonth] [tinyint] NOT NULL,
    [birthday] [tinyint] NOT NULL,
    [birthyear] [int] NOT NULL,
    [gender] [varchar](10) NULL,
    [city] [varchar](50) NULL,
    [state] [varchar](50) NULL,
    [country] [varchar](50) NULL,
    [registerdate] [datetime] NOT NULL,
    [editdate] [datetime] NULL,
    [confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3]  DEFAULT ((0)),
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
    [userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
    [email] 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
SET ANSI_PADDING OFF


/****** Object:  Table [dbo].[lastlogin]    Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON


DBO.SAVEDSEARCHES


CREATE TABLE [dbo].[savedsearches](
    [savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
    [searchname] [varchar](50) NOT NULL,
    [userid] [int] NOT NULL,
    [date] [datetime] NULL,
    [isdefault] [bit] NULL,
    [gender] [char](10) NULL,
    [startyear] [varchar](50) NULL,
    [endyear] [varchar](50) NULL,
    [country] [varchar](50) NULL,
    [miles] [int] NULL,
    [pictures] [varchar](50) NULL,
    [postal] [int] NULL,
    [sort] [tinyint] NULL,
    [photostring] [varchar](50) NULL,
    [orderby] [tinyint] NULL,
 CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
    [userid] ASC,
    [searchname] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches]  WITH NOCHECK ADD  CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO




The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.

INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))

DBO.LASTLOGIN





Code Snippet


CREATE TABLE [dbo].[lastlogin](
    [lastloginid] [int] IDENTITY(1,1) NOT NULL,
    [userid] [int] NOT NULL,
    [date] [datetime] NOT NULL,
    [status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status]  DEFAULT ((0)),
    [activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity]  DEFAULT (getutcdate()),
    [online]  AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
 CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
    [date] ASC,
    [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].[lastlogin]  WITH NOCHECK ADD  CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]

• Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
• Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At
• Unique Index Returns Duplicate Rows
• Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index
• SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
• SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
• Cannot Insert Duplicate Key Row In Object
• Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
• Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
• Unique Index Conflict On Insert Into
• How To Return Primary Unique Index Key On Insert
• BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?
• Violation Of PRIMARY KEY Constraint 'PK_tblType'. Cannot Insert Duplicate Key In Object 'dbo.tblType'.
• Error Number For Duplicate Column Value During An Insert For A Unique Column ?
• CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
• Unique Constraint Vs Unique Index In MS SQL 2000
• What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?
• Unique Constraint And Unique Index, What's The Difference?
• Unique Constraint Vs Unique Index
• Unique Index Vs Unique Constraint
• Unique Index Vs Unique Constraints
• What 's Difference Between Unique Key And Unique Index
• Duplicate Key But Unique Rows.
• Elimenating Duplicate Keys With Unique Row.
• Renaming Duplicate Row Data To Be Unique?
• Delete Duplicate Rows With No Unique Columns
• Unique Constraint Does Not Permit Duplicate NULL Values
• Unique Index
• Unique Index
• Unique Index
• Can't Add Bit Column To Unique Index
• Creating A Unique Index
• Unique Constraint Index
• UNIQUE INDEX If Not NULL
• Unique Constraint/index
• Better Performance With Unique Index?
• Double Unique Index
• Unique Index, SQL 2005
• PK Vs Unique Clustered Index
• Unique Index In Sql Server
• Unique Constraint/Index
• Msg 2714, Duplicate Object Error
• How To Create Unique Secondary Index ?? HELP
• Unique Index For Two Columns In A Table
• Create Unique Index Error
• Unique Index On X Columns But Not Primary Key
• Replication- Converts PK To Unique Index
• Unique Index With Null Values