Databases
StackOverflow2013
Postsdbo
POViolation of PRIMARY KEY on select statement
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POViolation of PRIMARY KEY on select statement
    text
    copied!<p>I have a table that looks like this:</p> <pre><code>CREATE TABLE [dbo].[SomeTable]( [Guid] [uniqueidentifier] NOT NULL, [Column1] [int] NOT NULL, [Column2] [datetime] NOT NULL, [Column3] [bit] NOT NULL, [Column4] [smallint] NOT NULL, [Column5] [uniqueidentifier] NULL, [Column6] [varchar](100) NULL, [Column7] [datetime] NULL, [Column8] [datetime] NULL, CONSTRAINT [pkSomeTable] PRIMARY KEY CLUSTERED ( [Guid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) </code></pre> <p>NOTE: The names have been changed to protect the innocent.</p> <p>Through a stored procedure, I am running this query:</p> <pre><code>SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8 FROM SomeTable WHERE SomeTable.Guid = @Guid ORDER BY SomeTable.Guid </code></pre> <p>After running this, I get a: Violation of PRIMARY KEY constraint 'pkSomeTable'. Cannot insert duplicate key in object 'dbo.SomeTable'. UniqueKeyConstraintException: Duplicate key.</p> <p>I'm very confused on how I can have an exception like this running only a select statement. There are no triggers on the table. Does anyone have an idea on how this can happen?</p> <p>Edit: The entire stp is:</p> <pre><code> CREATE PROCEDURE dbo.stpSelectSomeTable @Guid UNIQUEIDENTIFIER = NULL AS BEGIN SET NOCOUNT ON IF (@Guid is NULL) SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8 FROM SomeTable ORDER BY SomeTable.Guid Else SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8 FROM SomeTable WHERE SomeTable.Guid = @Guid ORDER BY SomeTable.Guid END GO </code></pre> <p>I am sure that GUID is not null when this STP is called. I am also sure that this is the statement that caused the problem. The stack trace of the exception shows me this. It turns out the database that this happened on has been deleted a restored to a previous version. Because of this, I can't test the statement. That was done before this issue was given to me.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload