CODEWITHSUNDEEP
sql-serversql-server-2008-r2
dwerner
dwerner

Reputation: 6602

Why would this table allow a PK id of 0?

In SQL Server 2008 R2, I have a table that allows 0 as a foreign key. This is odd because a primary key constraint is specified, and the IDENTITY(1,1) descriptor is also used.

When the first record is inserted to the table, it's PK (RegionID) is 0.

I don't have IDENTITY-INSERT turned ON when I am doing an insert. (Normal operation)

Here's the table definition:

CREATE TABLE [dbo].[tdfRegions](
    [RegionID] [int] IDENTITY(1,1) NOT NULL,
    [RegionName] [nvarchar](50) NOT NULL,
    [RegionDescription] [nvarchar](50) NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_tdfRegions] PRIMARY KEY CLUSTERED 
(
    [RegionID] 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

I have been acting under the assumption that the first record inserted to this table would have a RegionID of 1. 

INSERT INTO 
    tdfRegions (RegionName, RegionDescription, Active) 
VALUES 
    ('test','test', 1)

Produces:

RegionID   RegionName   RegionDescription   Active
    0      test         test                   1

Why might this be happening?

EDIT:

Ok, I've got a little more background information here. Someone ran a 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

To clear the database when it was first created. Could this have been responsible?

Upvotes: 0

Views: 144

Answers (2)

Kin Shah
Kin Shah

Reputation: 612

The only reason it you could end up with 0 in the RegionID is --

when you truncate the table and reseed the identity to 0 using below command 

DBCC CHECKIDENT('tdfRegions', RESEED, 0)

If you then insert into the table with your insert block, it will be RegionID = 0.

enter image description here

Upvotes: 1

jerrylagrou
jerrylagrou

Reputation: 511

The ways I know to reset the PK are:

a) use truncate
But that would put the see back to 1

b) use something like:
DBCC CHECKIDENT (MyTable, RESEED, 1)
If you used a 0 instead of a 1 in the last statement, that would get the seed set to 0.

Someother people talking about this.
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/ca0db3d2-e3ae-46ce-b8f0-bfc3bf95a509/

Upvotes: 1

Related Questions