none
SQL Server 2005 table loses primary key

    Question

  • 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.

    Thanks


    G.Waters

    Sunday, June 30, 2013 6:37 AM

Answers

  • 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

    • Marked as answer by George Waters Saturday, July 06, 2013 2:57 PM
    Monday, July 01, 2013 4:49 PM

All replies

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

    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Sunday, June 30, 2013 7:09 AM
  • If your primary key is lost, are you able to insert null values into the table?

    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Hallo Rajukiran,

    If you drop a Primary Key the constraint NOT NULL will not be dropped with it! That means that the attribute is not allowed to have NULL values.

    USE tempdb;
    Go
    
    IF OBJECT_ID('dbo.foo') IS NOT NULL
    	DROP TABLE dbo.foo;
    	GO
    
    CREATE TABLE dbo.foo
    (
    	Id	int		NOT NULL,
    	col1	char(20)	NOT NULL,
    	col2	char(20)	NOT NULL,
    
    	CONSTRAINT pk_foo_id PRIMARY KEY CLUSTERED (Id)
    );
    GO
    
    -- Let's have a look to the object definitions
    SELECT	index_id,
    		type_desc,
    		is_unique,
    		is_primary_key
    FROM	sys.indexes
    WHERE	object_id = OBJECT_ID('dbo.foo');
    
    SELECT	name, column_id, is_nullable
    FROM	sys.columns
    WHERE	object_id = OBJECT_ID('dbo.foo');
    
    -- Now I remove the constraint for the PK
    ALTER TABLE dbo.foo DROP CONSTRAINT pk_foo_id;
    GO
    
    SELECT	index_id,
    		type_desc,
    		is_unique,
    		is_primary_key
    FROM	sys.indexes
    WHERE	object_id = OBJECT_ID('dbo.foo');
    
    SELECT	name, column_id, is_nullable
    FROM	sys.columns
    WHERE	object_id = OBJECT_ID('dbo.foo');
    
    DROP TABLE dbo.foo;
    GO

    The above script creates a table with a pk on the [id] attribute. After the creation the information about the indexes and column properties will be displayed. Than the PK-constraint will be dropped and the column properties will be displayed again:

    As you can see from the pic above the first statement points to a clustered index while the second one reverts to a HEAP. The column property [is_nullable] didn't change.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Sunday, June 30, 2013 8:14 AM
  • 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;


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    • Edited by Uwe RickenMVP Sunday, June 30, 2013 8:28 AM Courtesy to name the TE by his first name :)
    Sunday, June 30, 2013 8:27 AM
  • 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.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Sunday, June 30, 2013 9:10 AM
  • No, that column was identity and primary key, so it seems the identity got lost

    G.Waters

    Sunday, June 30, 2013 11:41 AM
  • Thanks, I'm the developer and I still don't understand how the identity got lost, no one has access to database nor exposed to internet. The system always writes to that table and it has been running for 6 months without any problem until now.

    G.Waters

    Sunday, June 30, 2013 11:45 AM
  • 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!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Sunday, June 30, 2013 12:17 PM
  • 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;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 30, 2013 2:18 PM
    Moderator
  • 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?...


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 01, 2013 11:59 AM
    Moderator
  • 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.

    Monday, July 01, 2013 1:27 PM
    Moderator
  • 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?


    G.Waters

    Monday, July 01, 2013 1:30 PM
  • Thanks Tibor, but I am absolutely sure that no one has access to database but me. I am likely thinking that this could be a bug in Management Studio 2005

    G.Waters

    Monday, July 01, 2013 1:33 PM
  • Thanks Tom, but no, no one has access to database server but me.

    G.Waters

    Monday, July 01, 2013 1:34 PM
  • 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 :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, July 01, 2013 1:51 PM
  • SQL Server doesn't do this by itself. Perhaps you used the "Design table" dialog to do some schema change? This dialog will often re-create the table, even if a simple ALTER could have done it, and if something goes wrong in this script then you might end up with what you have. I've seen reports about similar things when using this GUI...

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 01, 2013 2:38 PM
    Moderator
  • 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

    • Marked as answer by George Waters Saturday, July 06, 2013 2:57 PM
    Monday, July 01, 2013 4:49 PM
  • 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.

    Monday, July 01, 2013 5:23 PM
    Moderator
  • 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.


    G.Waters

    Saturday, July 06, 2013 3:00 PM
  • "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! :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, July 06, 2013 3:29 PM
    Moderator