1

I'm trying to save a database diagram but I'm getting an error. How can I fix this?

Cannot insert the value NULL into column 'diagram_id', table
'SOFT.dbo.sysdiagrams'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed.
A status of 0 will be returned instead. (.Net SqlClient Data Provider)

  • Guessing you don't have the proper permissions. – Pete Alvin Sep 12 '18 at 14:14
  • Diagrams are a deprecated feature in SSMS in version 18 (still in preview) so will eventually be done away with - FYI to anyone from the future. – LowlyDBA Dec 14 '18 at 20:25
10

Hard to suggest a more specific fix or suggest a cause given that we have no background on how your system got into this state, what you did prior to trying to create the first diagram, if you have successfully created diagrams in the past, or even what version of SQL Server you're using. It sounds like your sysdiagrams table somehow lost the IDENTITY property on the diagram_id column. Do this:

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty 
  @name=N'microsoft_database_tools_support', 
  @value=1 , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

This will of course lose any diagrams you were previously able to save.

(After performing a search it saddened me to see that many people suggest as a fix to make the default for this column 0. This is great after you create one diagram but falls apart for multiple reasons once you try to create a second one.)

1

I was in a similar situation, unable to save a new diagram even though I had several others. In SSMS I manually added a new row to the sysdiagram table with the name of my new yet unsaved table. Then saved my new 'Diagram_0' with the name I'd put in the table. SSMS responded with the "table of this name already exists, do you want to save" question, answer yes to this, job done or rather problem avoided reasonably simply. Not problem solved I might add, so I'm sure there's a better answer out there, just didn't have time to find it.

0

I encountered the same issue and fixed it simply by opening dbo.sysdiagrams table in design mode and change the column diagram_id to "Is Identity", following the tip by Aaron.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.