I can't save Database Diagrams

Question

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)

Answer 1

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. If you really need to keep your existing diagrams, you can add the identity property back using the table designer (yuck), just make sure you set the initial seed higher than any existing value in the table and that you have enabled the setting "Prevent saving changes that require table re-creation." Note that you can't do this with ALTER TABLE, since it doesn't support turning on/off the IDENTITY property - feel free to script out the change and see all the gymnastics that happen behind the scenes.

(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.)

Answer 2

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.

Answer 3

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.