Hi All,

I'm using SQL Server 2005 Standard with SP3 and today I discovered that our system send a exception saying that cannot insert null values into one table,  so I went to the SQL managment studio and found out that the table's primary key is lost !!!, this primary key was configured as a unique automatic value, why this happened?, has anyone had the same problem?

I'm really worried about this, so any information would be appreciated.

If your primary key is lost, are you able to insert null values into the t

Hallo George,

If your relation had a Primary Key it implicits a NOT NULL constraint, too. If the PK has been dropped it will not change anything to the NOT NULL constraint. BUT...

I assume that the "automatic" value (IDENTIY) has been changed to a normal attribute. You have to ask your developers about the reasons. Basically it is quite complex to remove the IDENTITY. The following script will demonstrate the behaviour of your problem:

USE tempdb;
Go

IF OBJECT_ID('dbo.foo') IS NOT NULL
	DROP TABLE dbo.foo;
	GO

CREATE TABLE dbo.foo
(
	Id	int		NOT NULL IDENTITY (1, 1),
	col1	char(20)	NOT NULL,
	col2	char(20)	NOT NULL,

	CONSTRAINT pk_foo_id PRIMARY KEY CLUSTERED (Id)
);
GO

INSERT INTO dbo.foo (col1, col2)
VALUES
('A', 'A'),
('B', 'B')
GO

-- Create a new attribute for saving the id values
ALTER TABLE dbo.foo ADD [id_new] int NULL;
GO

-- Update the new column with the id values
UPDATE dbo.foo SET [id_new] = [Id];

--- Now I drop the constraint PK from the [id]
ALTER TABLE dbo.foo DROP CONSTRAINT pk_foo_id;

-- Now I drop the Id column
ALTER TABLE dbo.foo DROP COLUMN [Id];

-- And now I rename the [id_new] to [id]
EXEC sp_rename @objname = 'dbo.foo.Id_new', @newname = 'Id', @objtype = 'COLUMN';

-- Add the constraint NOT NULL to the new attribute
ALTER TABLE dbo.foo ALTER COLUMN [Id] int NOT NULL;
GO

-- recreate the clustered index (which is now not a PK!)
CREATE UNIQUE CLUSTERED INDEX pk_foo_id ON dbo.foo (Id);
GO

-- Now i try to add new records with the same process as previously
-- THIS WILL FAIL!
INSERT INTO dbo.foo (col1, col2)
VALUES
('A', 'A'),
('B', 'B')
GO

SELECT * FROM dbo.foo;
GO

DROP TABLE dbo.foo;		

Hi Uwe,

Yes, you are right. What I want to emphasize here is that even if the system send a exception saying that cannot insert null values into one table it doesn't depend on the primary key being there on the table.

Hallo George,

with deepest respect - but...

a DDL need ALTER privileges and..
nothing happens without a root cause :)

I would recommend to think about your security concept.
Maybe others will have privileges which allow manipulation schema data!

so I went to the SQL managment studio and found out that the table's primary key is lost !!!, this primary key was configured as a unique automatic value, why this happened?, has anyone had the same problem?

My guess is a DDL script was run that inadvertently removed the identity property.  I suggest you review the default trace for clues.  The script below will return all events from the default trace, including rollover files.

SELECT  
	 trace.TextData
	,trace.DatabaseName
	,trace.ObjectName
	,te.name AS EventName
	,trace.EventSubClass
	,trace.StartTime
	,trace.EndTime
	,trace.NTDomainName
	,trace.NTUserName
	,trace.HostName
	,trace.ApplicationName
	,trace.Spid
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
	FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
JOIN sys.trace_events AS te ON 
	trace.EventClass=te.trace_event_id
ORDER BY StartTime;

Perhaps somebody wanter to "copy" the table, using SELECT INTO or some other method which doesn't bring over the constrainst (and apparently NULLability) properly?...

As Tibor said,  the most likely reason is someone tried to copy the table using

SELECT * INTO NEWTABLE FROM TABLE

Then dropped TABLE and renamed NEWTABLE to TABLE.

This method does not copy keys or indexes to NEWTABLE and results in what you are seeing.

Dan,

Thanks for your help, well I ran the script you kindly sent and yes, it shows me that the table was altered, but it says it was from my laptop!!!!....which is highly improbable, no one has access to my laptop and no one has database passwords but me.

This makes me think that sometimes SQL Management Studio does rare things, I have worked with it for several years and found out that when doing lots of movements like queries, modify tables, create indexes between databases MS starts to have a weird behavior so I have to restart it, is it possible that MSQL MS could've caused this identity lost?, have you ever experienced a behavior like this?

Hi George,

NEVER heard about such a behaviour from SQL Server (neither 2005 nor later versions).
ALTER is a high privilege and not a common user privilege.

Can you check whether the indexes (if the relation had ones) are present in your relation

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('yourtable', 'U');

If the indexes are present than an SELECT INTO couldn't be the root cause. As Tom has pointed to - an INSERT INTO doesn't include indexes. If no indexes are present (allthough it should be) than this could be the root cause of the lost PK...

Otherwise - think about calling the Ghostbusters :)

OP, just another voice saying these things don't just happen on their own.

Note that SSMS has a default setting that won't even LET you accidentally save design changes to a table.

Options/Designers/PreventSavingChangesThatRequireTableRecreation.

Make sure you have that turned on!

And yes, I have "seen it happen" more than once, that tables "magically" lose their PKs and the like, but while I can't always figure when or why or how it happened, I'm certain it's always a loose nut behind the wheel, not evil pixies, well at least not evil pixies without SA privileges!

Josh

As others have said, this can happen from the GUI.  I have experienced this problem when the GUI tries to rewrite the table by creating a new table and errors for some reason.

Hi Josh

This option seems to exist only in SSMS 2008, but I think making changes to tables through this GUI is not a good choice, thanks.

"This option seems to exist only in SSMS 2008"

Correct. The option to disable schema changes using "Design Table" was introduced in SSMS 2008.

" but I think making changes to tables through this GUI is not a good choice"

I'm not arguing with that! :-)